Geek asks and answers

Using a counter to search for records with two or more different values ​​in a field

I have a simple question: how can I use Count (Distinct) in SQL (Oracle, to be precise) to return only rows that have two or more different values ​​in a given field.

This is easier to understand with an 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 representatives will be 246 and 456, and so I want the query result to show only accounts that are owned by 2 or more sellers

ACCOUNT     SALESMAN
246         Abc
246         Def
456         Abc
456         Def

Thank.

+5
source share
3 answers

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 demo .

+7

, HAVING, . 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

+5

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 sellers to use listagg:

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

Otherwise, Gareth's answer returns the results as you indicated in the question.

+1
source

More articles:


All Articles