Reputation: 644
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
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