CODEWITHSUNDEEP
sql
Mathias Jespers
Mathias Jespers

Reputation: 9

Selecting the amount of users that have no 'thing' assigned to it

I have a simplified table that shows my problem
Name      Animal
Bill          Dog
Bill           Cat
Bill           Fish
John         Cat
John        Fish
Sara         Dog
Sara         Cat
Mark         Fish

I want the number of people that have no dog. I tried this query. 

select count(distinct Name) from Table
where Animal <> 'Dog'



But it returns 4 and not the expected 2. What am I doing wrong?

Upvotes: 0

Views: 37

Answers (3)

N. Mittal
N. Mittal

Reputation: 66

your where condition "Animal <> 'dog'" is filtering the rows not the names.

So try the below query

Select count(distinct Name) from Table where name not in (
select Name from Table  where Animal = 'Dog')

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Use not exists :

select count(distinct t.name) as counts
from table t
where not exists (select 1 from table where name = t.name and animal = 'Dog');

For your current query you are filtering single record based on animal name which would not produced desired result as it should be along with name column.

Upvotes: 1

George Menoutis
George Menoutis

Reputation: 7240

Your query returned the count of names that have any animal other than dog.

select distinct name
from table t1
where not exists
(
    select 1 from table t2 where t1.name=t2.name and t2.Animal='Dog'
)

Upvotes: 1

Related Questions