Re: How do I solve this efficiently?

Konstantinos Agouros wrote:
>Hi,
>
>I have a table like the following :
> Table "expenses"
> Attribute | Type | Modifier
>-----------+-------------+----------
> datum | date |
> ware | varchar(80) |
> price | float4 |
>
>To get a summary of what I spent in a month I do a:
>select ware,sum(price) from ausgaben where datum >= '1-1-2000' and datum <
>'2-1-2000';
>
>Now I would like to have an overview for a year in the form:
>
>Ware January February March ...
>Food 50.0 40.0 60.0
>CDs. 20.0 40.0
>
>.....
>
>What's the easiest way of doing this?

I don't know if there's a better way, but you could do:

SELECT ware,
sum(CASE WHEN date_part('month',datum) = 1 THEN price ELSE NULL END) AS jan,
sum(CASE WHEN date_part('month',datum) = 2 THEN price ELSE NULL END) AS feb,
sum(CASE WHEN date_part('month',datum) = 3 THEN price ELSE NULL END) AS mar,
sum(CASE WHEN date_part('month',datum) = 4 THEN price ELSE NULL END) AS apr,
sum(CASE WHEN date_part('month',datum) = 5 THEN price ELSE NULL END) AS may,
sum(CASE WHEN date_part('month',datum) = 6 THEN price ELSE NULL END) AS jun,
sum(CASE WHEN date_part('month',datum) = 7 THEN price ELSE NULL END) AS jul,
sum(CASE WHEN date_part('month',datum) = 8 THEN price ELSE NULL END) AS aug,
sum(CASE WHEN date_part('month',datum) = 9 THEN price ELSE NULL END) AS sep,
sum(CASE WHEN date_part('month',datum) = 10 THEN price ELSE NULL END) AS oct,
sum(CASE WHEN date_part('month',datum) = 11 THEN price ELSE NULL END) AS nov,
sum(CASE WHEN date_part('month',datum) = 12 THEN price ELSE NULL END) AS dec
FROM expenses
GROUP BY ware;

It would probably be worth making it a view, I should think.

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Come now, and let us reason together, saith the LORD;
though your sins be as scarlet, they shall be as white
as snow; though they be red like crimson, they shall
be as wool." Isaiah 1:18