-
Most Common Value
I need help with a sub query that I am working on. I have a table that has dates, periods, users, and reasons. A user will have several rows with the same date and different periods and reasons. I need to be able to return the most common reason for a given date.
Date Per User Reason
08/08/2010 1 Johnson,A qx
08/08/2010 2 Johnson,A bc
08/08/2010 3 Johnson,A ay
08/08/2010 4 Johnson,A qx
08/08/2010 5 Johnson,A ay
08/08/2010 6 Johnson,A qx
08/08/2010 7 Johnson,A qx
Would return
Date User Reason
08/08/2010 Johnson,A qx
I have searched the forums to no avail, however I may be searching using the wrong terms.
Thanks for any help pointing me in the right direction.
-
What have you tried so far?
Search the docs for COUNT and GROUP BY.
-
I have tried count and group by and using the original example the query:
Select user, date, reason, count(reason)
From table
Group by user, date, reason
Gives me:
user date reason count(reason)
Johnson,A 08/08/2010 qx 4
Johnson,A 08/08/2010 bc 1
Johnson,A 08/08/2010 ay 2
I am stuck on how to only show the row that has the max count.
I may be going about this all wrong and if I need to start from a whole new direction that is fine.
Thanks
-
One option:
Code:
SQL> select * from table1;
DT P USERNAME RE
---------- - -------------------- --
08/08/2010 1 Johnson,A qx
08/08/2010 2 Johnson,A bc
08/08/2010 3 Johnson,A ay
08/08/2010 4 Johnson,A qx
08/08/2010 5 Johnson,A ay
08/08/2010 6 Johnson,A qx
08/08/2010 7 Johnson,A qx
7 rows selected.
SQL> with t as (
2 select username, dt, reason, count(reason) cnt, max(count(reason)) over () mx
3 from table1
4 group by username, dt, reason)
5 select dt, username, reason
6 from t
7 where cnt = mx;
DT USERNAME RE
---------- -------------------- --
08/08/2010 Johnson,A qx
-
SQL> SELECT * FROM COMMON;
DATES PERIOD NAME REASON
--------- --------- -------------------- ----------
08-AUG-10 1 Johnson qx
08-AUG-10 2 Johnson bc
08-AUG-10 3 Johnson ay
08-AUG-10 4 Johnson qx
08-AUG-10 5 Johnson qx
08-AUG-10 6 Johnson qx
08-AUG-10 7 Johnson qx
7 rows selected.
SQL> SELECT dates, name, reason, count(reason) from common
2 group by reason, name, dates
3 HAVING count(reason) IN (SELECT max(count(reason)) from common group by reason);
DATES NAME REASON COUNT(REASON)
--------- -------------------- ---------- -------------
08-AUG-10 Johnson qx 5
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|