Search code examples
sqlcountaggregate-functionshaving-clause

SQL - Counting Users That Have Multiple Inputs In Another Column

This should be easy, I'm just stuck.

I want to count the amount of users that have multiple accounts.

Example:

account | user
1         1
2         1
3         1
4         2
5         2
6         3
7         4
8         5
9         6
10        6

This would result in 3 users that have multiple accounts associated with their user id.

Solution

  • Use group by and having. If you want the list of users that have more than one account, then:

    select user
from mytable
group by user
having count(*) > 1

    This assumes no duplicates (user, account). Else, you need to change the having clause to:

    having count(distinct account) > 1

    Or:

    having min(account) <> max(account)

    Now, if you want the count of such users, just add another level of aggregation:

    select count(*) cnt
from (
    select user
    from mytable
    group by user
    having count(*) > 1
) t