Need Help with SQL Query in Access

Question

Hi Guys.

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 :)

Answer 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.

Answer 2

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"