1

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

3

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.

  • Works perfect! I need location lo to show the location next to the sum – tamaramaria Nov 14 '18 at 15:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.