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.
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 )
| 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 )
| PersonID | Cars | Pets | Age |
|----------|------|------|-----|
| 1 | 2 | 3 | 20 |
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments