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.Elphick@lfix.co.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