CODEWITHSUNDEEP
sqlms-accesscountgrouping
user3643304
user3643304

Reputation: 1

ACCESS Select Top 10 Records by COUNT per Group By

I am having trouble with this query in MS Access for Office 365. For each grouping I am trying to select the Top 10 rows by ItemCount. So top 10 ItemName counts will be shown per GroupID. I know the below is wrong because it will only query ten records. I am not sure where to go from here. I tried a subquery that ordered the items then attempted to pull out top 10 items but I obviously did it wrong.

Select Top 10 GroupID, ItemName, COUNT(ItemName) as ItemCount
FROM Table
GROUP BY GROUPID, ItemName
Order By COUNT(ItemName) DESC;

Any suggestions appreciated

Thanks!

Upvotes: 0

Views: 2105

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can use a correlated subquery. Here is one method:

select GroupID, ItemName, count(*) as ItemCount
from Table as t
group by groupid, ItemName
having count(*) in (select top 10 count(*)
                    from table as t2
                    where t2.groupid = t.groupid
                    group by t2.groupid, t2.itemname
                    order by count(*) desc
                   )
order by count(*) desc;

Note: If there are ties, you might get more than 10 rows for a given group.

Upvotes: 1

Eray Balkanli
Eray Balkanli

Reputation: 7960

Could you please try below, use the TOP keyword in a sub query.

Select GroupID, ItemName, COUNT(ItemName) as ItemCount
FROM Table t1
WHERE ItemName in (
                    Select TOP 10 t2.ItemName
                    from Table t2
                    where t2.GroupID = t1.GroupID
                    order by count(t2.ItemName) desc, t2.ItemName
                  )
GROUP BY GROUPID, ItemName
Order By GROUPID, COUNT(ItemName) DESC;

Upvotes: 0

Related Questions