I have some data with a general structure:
DATE CATEGORY VALUE ------------------------------ A X X' A Y X' A Z Z'
I want to output a single row like:
DATE VALUE_X VALUE_Y VALUE_Z TOTAL ------------------------------------------ A X' Y' Z' X'+Y'+Z'
I can see that the way to approach this is with a PIVOT, but how would I do this whilst also calculating the total?
Thank
Advertisement
Answer
You can use conditional aggregation:
select date, max(value) filter (where category = 'X') as x, max(value) filter (where category = 'Y') as y, max(value) filter (where category = 'Z') as z, sum(value) from t group by date;
Note: If you have values other than 'X'
, 'Y'
, and 'Z'
, then you might want either:
sum(value) filter (where category in ('X', 'Y', 'Z')
or:
where where category in ('X', 'Y', 'Z'
I am also assuming that the last column is actually for the sum. If you want a string aggregation, then the logic is:
string_agg(value, '+' order by category)