sql - How to use UNION with COUNT -

i have 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 , pets, 1 age.

i want count number of cars has, count number of pets has, , list age.

this have far:

select     car.personid person,     count(car.carid) cars,     null pets car car.personid = 1 group car.personid  union  select     pet.personid person,     null cars,     count(pet.petid) pets pet pet.personid = 1 group pet.personid 

this produces:

person | cars | pets -------|------|----- 1      | 2    | null 1      | null | 3 

but i'd results this:

person | cars | pets | age -------|------|------|---- 1      | 2    | 3    | 20 

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

i'm stuck on how bring records 1 row , add age column.

sql fiddle

query 1:

select p.personid,        ( select count(1) car c c.personid = p.personid ) cars,        ( select count(1) pet t t.personid = p.personid ) pets,        a.ageid age   person p        left outer join        age        on ( p.personid = a.personid ) 

results:

| personid | cars | pets | age | |----------|------|------|-----| |        1 |    2 |    3 |  20 | 

query 2:

with numberofpets (   select personid,          count(1) numberofpets     pet   group personid ), numberofcars (   select personid,          count(1) numberofcars     car   group personid ) select p.personid,        coalesce( numberofcars, 0 ) cars,        coalesce( numberofpets, 0 ) pets,        ageid age   person p        left outer join age 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 |