Thread: How do I solve this efficiently?

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 would like to avoid creating to many temporary tables if possible.

Konstantin

--
Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: elwood@agouros.de
Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185
----------------------------------------------------------------------------
"Captain, this ship will not sustain the forming of the cosmos." B'Elana Torres

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