getting different counts in one query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

getting different counts in one query

Post 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.
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post 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.
Post Reply