Need Help with SQL Query in Access

Question

I have been handed a database to run a few queries on.

For one query I have to find the top 10 applications, from two different tables with hundreds of records. then on row (11) I will need to SUM or Count the remaining records and name the row "Other".

I have worked out the following code so far.

SELECT TOP 10 ApplicationTbl.AppName, Count(*) AS SessionNos
FROM ApplicationTbl INNER JOIN SessionTbl ON ApplicationTbl.AppID = SessionTbl.AppID
GROUP BY ApplicationTbl.AppName;
ORDER BY Count(*) DESC;

I am displayed with 10 top records, but I know need to sum the remaining SessionNos together onto row 11 and rename the AppName to "Other"

Can anyone please help, or recommend anything.

FYI: I am using Access 2007 built in SQL View, and I know that there is limits to how much can be done.

I am not very good with SQL, its new to me.

Thanks :)

This sounds like the description of the output of a report, not the output of a single SQL query. You would have to use two separate queries and merge their output in the report render. I don't say that what you ask is ultimately impossible, but if it is possible, it would be a rude hack. — ron
– ron, Commented Feb 5, 2010 at 8:45
hm.. there was an answer posted using UNION which is a working hack, but it magically disappeared — ron
– ron, Commented Feb 5, 2010 at 8:50

Binary Worrier · Accepted Answer · 2010-02-05 08:45:53Z

1

What you need is
1) The Query above that Gets the top 10, call it Top10Apps.
2) A second Query that selects from the same tables above, but where the rows are not in Top10Apps, and sums the rows / returns the sums & aggrigates, with the "Other" tag. Call This SumOfNotTop10Apps
3) A third query that unions Top10App & SumOfNotTop10Apps

If that's not clear post some comments and I'll try to make it clearer.

answered Feb 5, 2010 at 8:45

Binary Worrier

51.8k20 gold badges144 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andrew Over a year ago

In the query that will hold the "Other" Apps don't forget to add a sub-query to filter the top 10. Below I am assuming that App name is unique. Here is the principle for a sub-query where appname not in (select appname from (your query above) as top10apps)

Andrew Over a year ago

A bit about UNION (unions) select col1, col2 from tbl1 union select mycol1, mycol2 from sometable That is the general layout. You can union an two or more queries as long as the number of columns in the select match and each column in the select matches on type e.g. int, char, etc

Ismail Over a year ago

The code above, list top 10 records which dont seem to be correct as to my code the top apps and their sessions are different and also the new column sort_col only duplicates numbers 1 and 2 on all rows and from row 11 as I wanted the record to be renamed to "Other" but its not adding all sessions for remaining applications after row 10 together. I dont mean to confuse you if I am. Please ask more questions i you need more info. Thank you and much appreciated.

Fionnuala · Accepted Answer · 2010-02-05 10:03:07Z

0

SELECT TOP 10 ApplicationTbl.AppName, Count(*) AS SessionNos
FROM ApplicationTbl 
INNER JOIN SessionTbl 
ON ApplicationTbl.AppID = SessionTbl.AppID
GROUP BY ApplicationTbl.AppName
ORDER BY Count(*) DESC

UNION ALL

SELECT "Other" AS AppName, Count(*) AS SessionNos
FROM (ApplicationTbl 
INNER JOIN SessionTbl 
ON ApplicationTbl.AppID = SessionTbl.AppID) 
LEFT JOIN (SELECT TOP 10 ApplicationTbl.AppName, Count(*) AS SessionNos
           FROM ApplicationTbl 
           INNER JOIN SessionTbl 
           ON ApplicationTbl.AppID = SessionTbl.AppID
           GROUP BY ApplicationTbl.AppName;
           ORDER BY Count(*) DESC)  AS s 
ON ApplicationTbl.AppName = s.AppName
WHERE s.AppName Is Null
GROUP BY "Other"

edited Feb 5, 2010 at 10:03

answered Feb 5, 2010 at 9:01

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

2 Comments

Ismail Over a year ago

O man, you are legend, thanks so much, that did it. Didnt work at first as due to the semi-column ; on the following line: SELECT "Other" AS AppName, Count() AS SessionNos FROM (ApplicationTbl INNER JOIN SessionTbl ON ApplicationTbl.AppID = SessionTbl.AppID) LEFT JOIN (SELECT TOP 10 ApplicationTbl.AppName, Count() AS SessionNos FROM ApplicationTbl INNER JOIN SessionTbl ON ApplicationTbl.AppID = SessionTbl.AppID GROUP BY ApplicationTbl.AppName; <<<<<<<_----- Thanks a million. :) Exactly what I wanted

David-W-Fenton Over a year ago

This is the kind of thing that reports are great for. On the other hand, I once had to deliver a plain tab-delimited file to a publisher that was used to create a 50-page table, and it had to have multiple header levels. That was substantially more complicated than your two levels, and I have no recollection at all of how I actually managed it!

Collectives™ on Stack Overflow

Need Help with SQL Query in Access

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related