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 calculate the number of cars that someone has, to count the number of pets and indicate their age.

This is what I still have:

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 gives:

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

But I would like the results to look like this:

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

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

I am completely obsessed with how to wrap records in a single row and add age columns.

Answer 1

SQL Fiddle

Request 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 |

Request 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 2

Petid carid

- SqlFiddle

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; 

carid Petid ,

- SqlFiddle

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 

Answer 3

, , , , . , , ? , ? .

. , , .

, "ID" . , "age" "age_value", "AgeID". AGE PERSON Age (not AgeID) .

eg.

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 4

Do you want to count the number of excellent number of cars / pets? If so, add a selection inside the counter.

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 5

You need to join the individual values, so your calculations are in 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

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