# Thread: Max Count(*) question

1. Registered User
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!

Steve.

2. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
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
•