Am I able to sum records together by type-CodePudding

Lets say my data looks like this

Animal  Number
dog     3
dog     5
dog     1
cat     2
cat     5
horse   2
horse   4

Lets lets say I want to combine dog and cat together with this data set but also show the original individual animals. I know I can just use sum to add the individual animals, but how do I combine cat & dog together.

Animal  Number
dog     9
cat     7
horse   6
cat dog 16

CodePudding user response：

select distinct
    coalesce(
        case when Animal in ('cat', 'dog') then 'cat dog' else Animal end,
        Animal) as Category,
    sum(Num) as "Number"
from T
group by grouping sets (
    (Animal),
    (case when Animal in ('cat', 'dog') then 'cat dog' else Animal end)
)

CodePudding user response：

Aggregate as normal and combine a separate aggregation using Union. Note your results have arbitrary ordering.

select animal, Sum(number) Number
from t
group by animal

union all

select 'cat dog', sum(Number)
from t
where Animal in ('cat', 'dog');

CodePudding user response：

The first idea that comes to my mind is a union.

select Animal, sum(Number) as Number
from my_table
group by Animal
union
select 'cat dog' as Animal, sum(Number) as Number
from my_table
where Animal in ('cat', 'dog')
group by Animal
;