Oracle Subselect Union Multiple Column

Question

I want to select and count all cats and dogs at one location;

location | cats | dogs |
new york | 150  | 66   |
paris    | 34   | 12   |

What I've got so far:

SELECT
    cats,dogs
FROM
    (select k.location, count(*) as cats from pet k, location lo where k.location= lo.id and k.pet= 'Cat' group by k.location)
    union
    (select k.location, count(*) as dogs from pet k, location lo where k.location= lo.id and k.pet= 'Dog' group by k.location)

But I only get one column for location and cats...

Answer 1

Try this then instead:

select
  k.location,
  sum((case when k.pet = 'Cat' then 1 else 0 end)) as cats,
  sum((case when k.pet = 'Dog' then 1 else 0 end)) as dogs,
from pet k, location lo where k.location= lo.id and k.pet in ('Cat', 'Dog')
group by k.location
;

I am not sure what you need location lo for, seems you have all the necessary information in pet k.