I have a SQL query result table like this :

Date,metric,value
1,x,2
2,x,3
2,y,5
3,y,8
3,z,9

And I would like to get the sum by day for each metric ( filling with 0 when not present) :

Date,x,y,z
1,2,0,0
2,3,5,0
3,0,8,9

I do not know beforehand the names of the metrics. At the moment I'm loading the results in python and reshaping using pandas but surely there is a PostgreSQL way to do it.

How to achieve the above in PostgreSQL ?

2 Answers

0
fa06 On

you can use conditional aggregation with case when expression

select date, 
       max(case when metric='x' then value end) as x,
       max(case when metric='y' then value end) as y,
       max(case when metric='z' then value end) as z
from tablename
group by date
0
LongBeard_Boldy On

you can use crosstab

 select * from crosstab('select date, metric ,value from metric_table order by date, metric ,value '
                       ,'select  metric from metric_table group by metric  order by metric') 
                  as ct( date integer ,y integer,x integer, z integer);

But beware, "as ct( date integer ,y integer,x integer, z integer)" part must be dynamically created before calling query, based on "select metric from metric_table group by metric order by metric" result set