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

Question

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.

Answer 1

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.

Answer 2

As the other answer has indicated you need to use HAVING, but not in the manor indicated. You need to join back to the original table after using HAVING

SELECT  DISTINCT T.Account, T.SalesMan
FROM    T
        INNER JOIN
        (   SELECT  Account
            FROM    T
            GROUP BY Account
            HAVING COUNT(DISTINCT SalesMan) > 1
        ) Dupes
            ON Dupes.Account = T.Account

SQL Fiddle

Answer 3

You can do this with a simple GROUP BY/HAVING query:

select account
from t
group by account
having count(distinct salesperson) > 1

This returns the accounts, so the result is different from what you specify. One way to get the sales people is to use listagg:

select account, listagg(salesperson, ',')
from t
group by account
having count(distinct salesperson) > 1

Otherwise, Gareth's answer returns the results the way you specified in the question.