CODEWITHSUNDEEP
sqlselectjoin
Jesper Evertsson
Jesper Evertsson

Reputation: 613

Select rows with the same count in a column from a table

I have a table that looks like this:

Person (Ssn, Name, Age, Petname)

I need to form a question that returns the name of all persons that have the same amount of pets as the person with Ssn = 1 (i.e. if the person with Ssn = 1 has Petname = "Zeus" in the Petname all the persons that also have a pet should be returned). I know the table design is stupid but it's from a school asignment and has to look like that.

This is what I've got so far. I think it's partly right but I can't seem to figure it out completely: 

SELECT Name 
FROM   Person 
WHERE (SELECT COUNT(Petname) FROM Person WHERE Ssn = '1')
       = (SELECT COUNT(Petname) FROM Person WHERE Ssn != '1');

Upvotes: 1

Views: 104

Answers (3)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20499

This should do the trick:

SELECT Name
FROM Person
WHERE SSN <> 1
GROUP BY Name
HAVING COUNT(PetName) = (SELECT COUNT(PetName) FROM PERSON WHERE SSN='1')

Here is also a SQLFiddle with the code.

Upvotes: 2

Madhivanan
Madhivanan

Reputation: 13700

Try this

select Name from Person 
group by Name 
having 
sum(case when Pnr = '1' then 1 else 0 end) = sum(case when Pnr <> '1' then 1 else 0 end)

Upvotes: 0

bleeeah
bleeeah

Reputation: 3604

Assuming one person has only one and only one pet.

SELECT 
    Name
FROM 
    Person 
WHERE 
    PetName = (SELECT Petname FROM Person WHERE Ssn = 1)
    AND Ssn <> 1

Upvotes: 0

Related Questions