Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2004
    Location
    Ft. Lauderdale, FL
    Posts
    1

    Unanswered: Max Count(*) question

    I have a max(count(*)) sort of question.

    Table ACCOUNT_PRODUCTS has:

    Code:
    ACCOUNT_PRODUCT_ID, ACCOUNT_NUMBER, PRODUCT_ID
    Table PRODUCTS has:

    Code:
    PRODUCT_ID, PRODUCT_NAME, PRODUCT_CLASS
    I need to get back a result set of ACCOUNT_NUMBERS and the PRODUCT_CLASS that is the one that is represented most often for the ACCOUNT_NUMBER.

    I.e.,

    Acct1 has 3 products in Class "Q", 2 in Class "W" and 5 in Class "C"
    Acct2 has 1 product in Class "V" and 1 in Class "S"

    Results Set should pick first product class if the count is the same and return:

    Code:
    Acct1, C
    Acct2, V
    I am baffled!

    Thanks in advance,

    Steve.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Break the problem into logical steps:

    1) Get the counts by account and class:

    Code:
    select account, class, count(*) cnt
    from ...
    group by account, class;
    2) Get the max count per account:

    Code:
    select account, max(cnt) maxcnt
    from
    ( select account, class, count(*) cnt
      from ...
      group by account, class
    )
    group by account;
    3) Get details of account and class for those max(cnt) values:

    Code:
    select account, class
    from 
    ( select account, class, count(*) cnt
      from ...
      group by account, class
    )
    where (account, cnt) in
    ( select account, max(cnt) maxcnt
      from
      ( select account, class, count(*) cnt
        from ...
        group by account, class
      )
      group by account
    );
    Now that does look messy. If your DBMS supports the WITH clause then you can rewrite as:

    Code:
    with temp as
    ( select account, class, count(*) cnt
      from ...
      group by account, class
    )
    select account, class
    from temp
    where (account, cnt) in
    ( select account, max(cnt) maxcnt
      from temp
      group by account
    );

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •