Search code examples
sqloracle-databasecountaggregate-functions

Using count distinct to find records with 2 or more different values in a field

I have a simple question: How can I use Count(Distinct) in SQL (Oracle to be exact) to return only the rows where there are two or more different values in a given field.

This is easier understood by example:

ACCOUNT     SALESMAN
123         Abc
123         Abc

246         Abc
246         Def
246         Def

369         Hij

456         Abc
456         Def

In this example, the only Accounts with 2 different sales reps would be 246 and 456, and thus, I'd want the query's result to just show the the accounts shared by 2 or more salesmen:

ACCOUNT     SALESMAN
246         Abc
246         Def
456         Abc
456         Def

Thanks.

Solution

  • use having :

    select distinct account,salesman 
from MyTable where account in
(
    select account
    from MyTable
    group by account
    having count(distinct salesman) >= 2
)
order by 1,2

    Here is a demonstration.