Page 1 of 1

getting different counts in one query

Posted: Thu Mar 10, 2005 12:28 pm
by magicrobotmonkey
ok lets say I have two tables person and pet and I want to find out how many cats and dogs each person has. So I do this

Code: Select all

SELECT Person_Name, COUNT(Cats.PetID) AS cat_count, COUNT(Dogs.PetID) AS dog_count
FROM Person
LEFT JOIN Pets Cats ON Cats.OwnerID = Person.Person_ID AND Cats.Type = CAT
LEFT JOIN Pets Dogs ON Dogs.OwnerID = Person.Person_ID AND Dogs.Type = DOG
The problem I am having is that The counts aren't working. If there is only one dog, but two cats, I still get a count of two dogs.

Posted: Thu Mar 10, 2005 2:06 pm
by magicrobotmonkey
well, this is my stopgap method:

Code: Select all

SELECT Person_Name, COUNT(Cats.PetID) AS cat_count, COUNT(DISTINCT(Dogs.PetID)) AS dog_count
FROM Person
LEFT JOIN Pets Cats ON Cats.OwnerID = Person.Person_ID AND Cats.Type = CAT
LEFT JOIN Pets Dogs ON Dogs.OwnerID = Person.Person_ID AND Dogs.Type = DOG
of course, I'd rather find the 'correct' solution.