Max Count(*) question

shoczak · #1 (**permalink**) 10-20-04, 17:38

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.

andrewst · #2 (**permalink**) 10-21-04, 05:24

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
);

Internet Services

Web Development

Digital Marketing

Consumer Tech