Aleksey Papushin Aleksey Papushin - 1 year ago 57
SQL Question

How to find rows where the same column has only the same another column

Suppose following table:

Name Age Occupation
Alex 20 Student
Alex 20 Seller
Alex 20 Minister
Liza 19 Student
Liza 20 Volunteer
Liza 21 HR partner


I want to find names which have only (and only) 20 in age column. So from this table I want to get all "Alex" rows and no "Liza" rows at all.
Thanks!

Answer Source

You need to use Group By and Having clause. Try this way

select Name
from table
group by Name
having count(case when Age = 20 then 1 end) = count(*)

count(case when Age = 20 then 1 end) counts only when age = 20 if it is equal to total count then the name has only 20 as age.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download