Query Help. COUNT with DISTINCT

[wolves]

When I run this query, I get the result that I need.

Select PHONE, COUNT(DISTINCT(NAME)) from TABLE
where PHONE = '1234567879'
group PHONE
Having COUNT(DISTINCT(NAME)) > 1

PHONE                  COUNT(DISTINCT(NAME))
-------------------- ---------------------------
123456789                                      3

But, when I run this query, I am trying to also list the NAMES that are distinct, that are > 1, why do I get NO ROWS RETURNED?

Select NAME, PHONE, COUNT(DISTINCT(NAME)) from TABLE
where PHONE = '123456789'
group by NAME, PHONE
Having COUNT(DISTINCT(NAME)) > 1

no rows selected

 

[carp]

When you group by name and phone, your count of DISTINCT(name) will automatically be 1.

Try this:

SELECT name, phone
FROM table t, (Select PHONE, COUNT(DISTINCT(NAME)) 
                 from  TABLE
                group PHONE
               Having COUNT(DISTINCT(NAME)) > 1) v
WHERE t.phone = v.phone
ORDER BY 2,1;

 

[wolves]

Thanks, will give it a try and see how it works.