How to use UNION with COUNT

Question

I have this table structure:

TABLE: PERSON     TABLE: CAR

PersonID          PersonID | CarID
------            ---------|---------
1                 1        | 51
                  1        | 52


TABLE: PET          TABLE: AGE

PersonID | PetID    Person | AgeID
---------|----      -------|----
1        | 81       1      | 20
1        | 82
1        | 81

One person can have many cars and pets, but only one age.

I want to count the number of cars someone has, count the number of pets someone has, and list their age.

This is what I have so far:

select
    car.personid as person,
    count(car.carid) as cars,
    null as pets
from car
where car.personid = 1
group by car.personid

union all

select
    pet.personid as person,
    null as cars,
    count(pet.petid) as pets
from pet
where pet.personid = 1
group by pet.personid

This produces:

Person | Cars | Pets
-------|------|-----
1      | 2    | null
1      | null | 3

But I'd like the results to look like this:

Person | Cars | Pets | Age
-------|------|------|----
1      | 2    | 3    | 20

There's a fiddle here: http://sqlfiddle.com/#!3/f584a/1/0

I'm completely stuck on how to bring the records into one row and add the age column.

Answer 1

Do you want to count distinct number of cars/pets? If so, add a distinct within the count.

select
person.personid as person,
count(car.carid) as cars,
count(pet.petid) as pets
age.ageID
from person 
left outer join pet on pet.personid = person.personid
left outer join car on car.personid = person.personid 
left outer join age on age.personid = person.personid
where car.personid = 1
group by car.personid, age.ageID;

Answer 2

One issue I see with most of these responses is that they will only include people who own a car. What if the person doesn't have a vehicle, but has pets? What if they haven't entered their age, yet? You'd lose that metric.

Tie the person table into this as the main requirement. To get the rest of the numbers you could take various approcahes, such as a simple series of left outer joins on the other tables and count their result.

Also note that tagging "ID" at the end of values is a misnomer and considered bad design practice. If it's an age, just call it "age" or "age_value", but not "AgeID". I would also suggest denormalizing your AGE and PERSON tables and make Age (not AgeID) a nullable field.

E.G.

SELECT
  PERSON.PersonID,
  AgeID AS Age,
  CarCount,
  PetCount
FROM
  #PERSON AS PERSON
  LEFT OUTER JOIN AGE AS AGE
    ON AGE.PersonID = PERSON.PersonID
  LEFT OUTER JOIN
    ( SELECT PersonID, COUNT( 1 ) AS CarCount FROM CAR GROUP BY PersonID ) AS CAR
    ON CAR.PersonID = PERSON.PersonID
  LEFT OUTER JOIN
    ( SELECT PersonID, COUNT( 1 ) AS PetCount FROM PET GROUP BY PersonID ) AS PET
    ON PET.PersonID = PERSON.PersonID

Answer 3

Another method is

select person,
sum(cars) as cars,
sum(pets) as pets
from
(
  select
    car.personid as person,
    count(car.carid) as cars,
    null as pets
from car
where car.personid = 1
group by car.personid

union all

select
    pet.personid as person,
    null as cars,
    count(pet.petid) as pets
from pet
where pet.personid = 1
group by pet.personid
) as t
group by person

Answer 4

SQL Fiddle

Query 1:

SELECT p.PersonID,
       ( SELECT COUNT(1) FROM CAR c WHERE c.PersonID = p.PersonID ) AS Cars,
       ( SELECT COUNT(1) FROM PET t WHERE t.PersonID = p.PersonID ) AS Pets,
       a.AgeID AS Age
FROM   PERSON p
       LEFT OUTER JOIN
       AGE a
       ON ( p.PersonID = a.PersonID )

Results:

| PersonID | Cars | Pets | Age |
|----------|------|------|-----|
|        1 |    2 |    3 |  20 |

Query 2:

WITH numberOfPets AS (
  SELECT PersonID,
         COUNT(1) AS numberOfPets
  FROM   PET
  GROUP BY PersonID
),
numberOfCars AS (
  SELECT PersonID,
         COUNT(1) AS numberOfCars
  FROM   CAR
  GROUP BY PersonID
)
SELECT p.PersonID,
       COALESCE( numberOfCars, 0 ) AS Cars,
       COALESCE( numberOfPets, 0 ) AS Pets,
       AgeID AS Age
FROM   PERSON p
       LEFT OUTER JOIN AGE a ON ( p.PersonID = a.PersonID )
       LEFT OUTER JOIN numberOfPets t ON ( p.PersonID = t.PersonID )
       LEFT OUTER JOIN numberOfCars c ON ( p.PersonID = c.PersonID )

Results:

| PersonID | Cars | Pets | Age |
|----------|------|------|-----|
|        1 |    2 |    3 |  20 |

Answer 5

You need to be joining on single values, so do your counts within subqueries

select c.PersonID,a.CarID,b.PetID,c.AgeID from (
            select person.PersonID, COUNT(car.CarID) as CarID
            from Person INNER JOIN Car on Person.PersonID = Car.PersonID
                  group by Person.PersonID) a
            inner join (
            select person.PersonID, COUNT(Pet.PetID) as PetID
            from Person INNER JOIN Pet on Person.PersonID = Pet.PersonID
                  group by Person.PersonID) b
            on a.PersonID = b.PersonID
            inner join (select PersonID,AgeID from Age) c
            on a.PersonID = c.PersonID

Answer 6

Should work with duplicate Petid or duplicate carid

SqlFiddle Demo

WITH person_cte 
     AS (SELECT * 
         FROM   person), 
     car_count 
     AS (SELECT Count(1) AS car, 
                p.personid 
         FROM   person_cte p 
                LEFT OUTER JOIN car c 
                             ON p.personid = c.personid 
         GROUP  BY p.personid), 
     pet_count 
     AS (SELECT Count(1) AS Pet, 
                p.personid 
         FROM   person_cte p 
                LEFT OUTER JOIN pet c 
                             ON p.personid = c.personid 
         GROUP  BY p.personid) 
SELECT c.personid, 
       c.car, 
       p.pet, 
       a.ageid 
FROM   car_count c 
       INNER JOIN age a 
               ON c.personid = a.personid 
       INNER JOIN pet_count p 
               ON p.personid = c.personid; 

If there wont be any duplicates in Carid or Petid then use this

SqlFiddle Demo

SELECT p.personid, 
       a.ageid, 
       Count(DISTINCT carid) as carid, 
       Count(DISTINCT petid) as petid
FROM   person p 
       INNER JOIN age a 
               ON p.personid = a.personid 
       LEFT OUTER JOIN car c 
                    ON p.personid = c.personid 
       LEFT OUTER JOIN pet pe 
                    ON p.personid = pe.personid 
GROUP  BY p.personid, 
          a.ageid