SQL Query to Count() multiple tables

Question

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 like a query that returns details of the person,the number of pets, cars and children they have e.g.

Person.Name   Count(cars) Count(children) Count(pets)

John Smith    3           2               4
Bob Brown     1           3               0

What is the best way to do this?

OMG Ponies · Accepted Answer · 2009-09-04 15:42:23Z

Subquery Factoring (9i+):

WITH count_cars AS (
    SELECT t.person_id
           COUNT(*) num_cars
      FROM CARS c
  GROUP BY t.person_id),
     count_children AS (
    SELECT t.person_id
           COUNT(*) num_children
      FROM CHILDREN c
  GROUP BY t.person_id),
     count_pets AS (
    SELECT p.person_id
           COUNT(*) num_pets
      FROM PETS p
  GROUP BY 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
LEFT JOIN (SELECT t.person_id
                  COUNT(*) num_cars
             FROM CARS c
         GROUP BY t.person_id) cars ON cars.person_id = t.person_id
LEFT JOIN (SELECT t.person_id
                  COUNT(*) num_children
             FROM CHILDREN c
         GROUP BY t.person_id) children ON children.person_id = t.person_id
LEFT JOIN (SELECT p.person_id
                  COUNT(*) num_pets
             FROM PETS p
         GROUP BY p.person_id) pets ON pets.person_id = t.person_id

Vincent Malgrat · Accepted Answer · 2009-12-02 14:01:09Z

5

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
  LEFT JOIN car ON (person.id = car.person_id)
  LEFT JOIN child ON (person.id = child.person_id)
  LEFT JOIN pet ON (person.id = pet.person_id)
 GROUP BY person.name

edited Dec 2, 2009 at 14:01

answered Sep 4, 2009 at 15:01

Vincent Malgrat

67.9k9 gold badges122 silver badges176 bronze badges

1 Comment

jvangeld Over a year ago

Voted for this one because it is very straightforward.

Eric Petroelje · Accepted Answer · 2009-09-04 14:53:54Z

1

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
   GROUP BY 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
   GROUP BY 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
   GROUP BY Person.PersonID
) PersonPets ON PersonPets.PersonID = p.PersonID

answered Sep 4, 2009 at 14:53

Eric Petroelje

60.7k9 gold badges133 silver badges178 bronze badges

Comments

Dirk · Accepted Answer · 2009-09-04 14:51:17Z

0

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
ORDER BY p.name

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

answered Sep 4, 2009 at 14:51

Dirk

31.1k8 gold badges86 silver badges103 bronze badges

4 Comments

Eric Petroelje Over a year ago

This is the simplest solution, but would likely result in poor performance due to the correlated subqueries. If your database is small though, it may not matter.

paweloque Over a year ago

interesting to see that you give your technical answer your personal flavour ;)

paweloque Over a year ago

@Eric: you're right, however, the db will probably rewrite this statement. So finally it might become what you proposed.

Dirk Over a year ago

Sleep deprivation can cause loss of self-restraint :-)

Alexey Sviridov · Accepted Answer · 2009-09-04 14:51:51Z

0

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

answered Sep 4, 2009 at 14:51

Alexey Sviridov

3,49031 silver badges34 bronze badges

Comments

Doug Hays · Accepted Answer · 2009-09-04 14:53:52Z

0

You could do this with three outer joins:

SELECT
    Person.Name,
    sum(case when cars.id is not null then 1 else 0 end) car_count,
    sum(case when children.id is not null then 1 else 0 end) child_count,
    sum(case when pets.id is not null then 1 else 0 end) pet_count
FROM
    Person
LEFT OUTER JOIN
    cars on
    Person.id = cars.person_id
LEFT OUTER JOIN
    children on
    Person.id = children.person_id
LEFT OUTER JOIN
    pets on
    Person.id = pets.person_id
GROUP BY
    Person.Name

I belive that Oracle now supports the case when syntax, but if not you could use a decode.

answered Sep 4, 2009 at 14:53

Doug Hays

1,51711 silver badges13 bronze badges

3 Comments

OMG Ponies Over a year ago

Oracle has supported CASE statements since 9i.

Doug Hays Over a year ago

Rexem, Thanks for the clarification! I've written some nasty decode statements and would have loved to have been able to use the case statement! Doug.

Daniel Emge Over a year ago

You can also just use count(cars.id), etc. It won't count null values.

Pete OHanlon · Accepted Answer · 2009-09-04 20:19:12Z

-1

You'd need to include multiple count statements in the query. Off the top of my head,

SELECT  p.Name, COUNT(DISTINCT t.Cars), COUNT(DISTINCT o.Children), Count(DISTINCT p.Pets)
FROM Person p
INNER JOIN Transport t ON p.ID = t.PersonID
LEFT JOIN Offspring o ON p.ID = o.PersonID
LEFT JOIN Pets p ON p.ID = o.OwnerID
GROUP BY p.Name
ORDER BY p.Name

edited Sep 4, 2009 at 20:19

answered Sep 4, 2009 at 14:54

Pete OHanlon

9,1542 gold badges31 silver badges28 bronze badges

Collectives™ on Stack Overflow

SQL Query to Count() multiple tables

7 Answers 7

Comments

1 Comment

Comments

4 Comments

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

1 Comment

Comments

4 Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related