Thread: group by and one column per month?

given a table with these fields:

dealer char()
revenue float()
vdate date

now:

select dealer, sum(revenue)
    from table
group by dealer

and also:

select dealer, sum(revenue)
    from table where date_part('month', vdate) = 1
group by dealer

work fine.

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?

TIA
Harald
--
Harald Lux                      lux@lux.de
Sandkaule 5-7                   Tel.: +49 228 692325
D-53111 Bonn                    http://www.lux.de/

02-01-30 22.10   Harald Lux   info@lux.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.andersson@embryo.se
---------------------------------------------------
"Att idag tänka annorlunda än igår skiljer den vise från den envise." — John
Steinbeck