Sql Query To Count() Multiple Tables

I have a table which has several one to many relationships with other tables. Let's say the main table is a person, and the other tables represent pets, cars and children. I would

Solution 1:

Subquery Factoring (9i+):

WITH count_cars AS (
    SELECT t.person_id
           COUNT(*) num_cars
      FROM CARS c
  GROUPBY t.person_id),
     count_children AS (
    SELECT t.person_id
           COUNT(*) num_children
      FROM CHILDREN c
  GROUPBY t.person_id),
     count_pets AS (
    SELECT p.person_id
           COUNT(*) num_pets
      FROM PETS p
  GROUPBY p.person_id)
   SELECT t.name,
          NVL(cars.num_cars, 0) 'Count(cars)',
          NVL(children.num_children, 0) 'Count(children)',
          NVL(pets.num_pets, 0) 'Count(pets)'FROM PERSONS t
LEFT JOIN count_cars cars ON cars.person_id = t.person_id
LEFT JOIN count_children children ON children.person_id = t.person_id
LEFT JOIN count_pets pets ON pets.person_id = t.person_id

Using inline views:

SELECT t.name,
          NVL(cars.num_cars, 0) 'Count(cars)',
          NVL(children.num_children, 0) 'Count(children)',
          NVL(pets.num_pets, 0) 'Count(pets)'FROM PERSONS t
LEFTJOIN (SELECT t.person_id
                  COUNT(*) num_cars
             FROM CARS c
         GROUPBY t.person_id) cars ON cars.person_id = t.person_id
LEFTJOIN (SELECT t.person_id
                  COUNT(*) num_children
             FROM CHILDREN c
         GROUPBY t.person_id) children ON children.person_id = t.person_id
LEFTJOIN (SELECT p.person_id
                  COUNT(*) num_pets
             FROM PETS p
         GROUPBY p.person_id) pets ON pets.person_id = t.person_id

Solution 2:

you could use the COUNT(distinct x.id) synthax:

SELECT person.name, 
       COUNT(DISTINCT car.id) cars, 
       COUNT(DISTINCT child.id) children, 
       COUNT(DISTINCT pet.id) pets
  FROM person
  LEFTJOIN car ON (person.id = car.person_id)
  LEFTJOIN child ON (person.id = child.person_id)
  LEFTJOIN pet ON (person.id = pet.person_id)
 GROUPBY person.name

Solution 3:

I would probably do it like this:

SELECT Name, PersonCars.num, PersonChildren.num, PersonPets.num
FROM Person p
LEFT JOIN (
   SELECT PersonID, COUNT(*) as num
   FROM Person INNER JOIN Cars ON Cars.PersonID = Person.PersonID
   GROUPBY Person.PersonID
) PersonCars ON PersonCars.PersonID = p.PersonID
LEFT JOIN (
   SELECT PersonID, COUNT(*) as num
   FROM Person INNER JOIN Children ON Children.PersonID = Person.PersonID
   GROUPBY Person.PersonID
) PersonChildren ON PersonChildren.PersonID = p.PersonID
LEFT JOIN (
   SELECT PersonID, COUNT(*) as num
   FROM Person INNER JOIN Pets ON Pets.PersonID = Person.PersonID
   GROUPBY Person.PersonID
) PersonPets ON PersonPets.PersonID = p.PersonID

Solution 4:

Note, that it depends on your flavour of RDBMS, whether it supports nested selects like the following:

SELECT p.name AS name
   , (SELECT COUNT(*) FROM pets e WHERE e.owner_id = p.id) AS pet_count
   , (SELECT COUNT(*) FROM cars c WHERE c.owner_id = p.id) AS world_pollution_increment_device_count
   , (SELECT COUNT(*) FROM child h WHERE h.parent_id = p.id) AS world_population_increment
FROM person p
ORDERBY p.name

IIRC, this works at least with PostgreSQL and MSSQL. Not tested, so your mileage may vary.

Solution 5:

Using subselects not very good practice, but may be here it will be good

select p.name, (select count(0) from cars c where c.idperson = p.idperson), 
               (select count(0) from children ch where ch.idperson = p.idperson),
               (select count(0) from pets pt where pt.idperson = p.idperson)
  from person p