Re: group by and one column per month?

02-01-30 22.10 Harald Lux info(at)lux(dot)de

> But instead of one column with the whole revenue (first example) or the
> renvenue of one month
> (second example) I would like to have a column for each month. is this
> possible within one select
> statement?

It is possible if you create virtual tables for each month. Somthing like
the following should work:

SELECT t.dealer, sum(jan.revenue) AS jan, ......, sum(dec.revenue) AS dec
FROM table t
INNER JOIN (SELECT dealer, revenue, from table where date_part('month',
vdate) = 1) AS jan USING(dealer)
.....
INNER JOIN (SELECT dealer, revenue, from table where date_part('month',
vdate) = 12) AS dec USING(dealer)
GROUP BY dealer;

Regards,

Torbjörn Andersson
---------------------------------------------------
Embryo Communication phone: +46 (0) 31-774 39 11(00)
Kungsgatan 7a fax: +46 (0)31 774 07 80
S-411 19 Göteborg mobile: 0708-30 70 04
Sweden home: http://www.embryo.se/
mail: torbjorn(dot)andersson(at)embryo(dot)se
---------------------------------------------------
"Att idag tänka annorlunda än igår skiljer den vise från den envise." — John
Steinbeck