CODEWITHSUNDEEP
sqlms-access
Leroy
Leroy

Reputation: 644

Get max count of each group

I have a table in MS access with 2 fields (payroll and absenceReason). There is one record per instance of absence.

I am trying to write an SQL query to return the most frequent absence reason for each person. I'm sure there's a simple way to do this but I can't quite seem to get it. I've tried writing the below query:

SELECT payroll, reason, max(count)
FROM
(
SELECT payroll, reason, count(reason) as count
FROM table1
GROUP BY payroll, reason
) 
GROUP BY payroll, reason

but this returns every reason for each person. If I remove the reason from the GROUP BY clause then it doesn't work as I haven't aggregated reason in the select part. Can anyone point out where I'm going wrong?

Thanks

Upvotes: 1

Views: 3081

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

One method uses having: 

SELECT payroll, reason, count(reason) as count
FROM table1 as t1
GROUP BY reason, payroll
HAVING t1.reason = (SELECT TOP 1 tt1.reason
                    FROM table1 as tt1
                    WHERE tt1.payroll = t1.payroll
                    GROUP BY tt1.reason
                    ORDER BY COUNT(*) DESC, tt1.reason
                   );

Upvotes: 1

Related Questions