Most Common Value
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Most Common Value

  1. #1
    Join Date
    Dec 2009
    Posts
    4

    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.

  2. #2
    Join Date
    Apr 2006
    Posts
    377
    What have you tried so far?

    Search the docs for COUNT and GROUP BY.

  3. #3
    Join Date
    Dec 2009
    Posts
    4
    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

  4. #4
    Join Date
    Apr 2006
    Posts
    377
    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

  5. #5
    Join Date
    May 2010
    Posts
    2
    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
  •  



Click Here to Expand Forum to Full Width