Postgres add column category to view base on sum of specific column-CodePudding

I have the animaltable table:

id	dog_amount	cat_amount	bird_amount
1	4	4	6
2	2	4	5
3	2	1	3

and i wonna create view like this:

id	animal	total
1	dogs	8
2	cats	9
3	birds	14

How can I achieve that? How to add the extra column "animal" to the view?

CodePudding user response：

UNION ALL the different animals, in a derived table (i.e. the subquery.) GROUP BY its result.

create view animalview as

select animal, count(*) total
from
(
    select 'dogs' animal, dog_amount   from animaltable
    UNION ALL 
    select 'cats' animal, cat_amount   from animaltable
    UNION ALL
    select 'birds' animal, bird_amount from animaltable
) dt
group by animal