PSQL, sum or count values grouped by a new set characteristic-CodePudding

I have a data table that looks like

name   breed
Mina   Persian
Lara   Asian
Mauri  Labrador
George Huskey 
Patri  Golden Retriever

and I would like to count by a new variable related to the breed, in this case I would like to count Persian, Asian as Cats and Labrador, Huskey, Golden Retriever as Dogs. So my output is:

breed  count 
Cats   2
Dogs   3

I have something like:

select 
  a.name,
  a.stage,
  count(*)
from 
  animals a 
group by
  a.breed

CodePudding user response：

You can create an additional column species with a case statement, and then perform a group by and count on top of that.

If your mysql version supports CTEs (common table expressions):

with animal_species as (
    select *, 
           case when breed in ('Persian', 'Asian') then 'Cats'
                when breed in ('Labrador', 'Huskey', 'Golden Retriever') then 'Dogs'
                else 'Unknown'
           end as species
    from animals 
)
select species, count(*) 
from animal_species 
group by species

If your mysql version does not support CTEs:

select species, count(*)
from (
    select *, 
               case when breed in ('Persian', 'Asian') then 'Cats'
                    when breed in ('Labrador', 'Huskey', 'Golden Retriever') then 'Dogs'
                    else 'Unknown'
               end as species
    from animals 
) a
group by species