am Fri, dem 06.10.2006, um 15:39:14 +0200 mailte Jan Danielsson folgendes:
> Hi,
>
> I have a table, which essentially is:
>
> transactions (
> id serial,
> amount numeric(8,2),
> dt date
> )
>
> I use this to keep track of my expenses. I want to take out the
> maximum expense for a date/week/month/year. But let's just focus on a
Okay. You can use extract() to extract a date/week/month from a date,
and group by on this value. An example:
test=# select * from tx;
id | amount | dt
----+--------+------------
1 | 100.00 | 2006-08-01
2 | 200.00 | 2006-09-01
3 | 300.00 | 2006-10-01
4 | 310.00 | 2006-10-02
5 | 320.00 | 2006-10-03
6 | 400.00 | 2006-10-13
(6 rows)
test=# select extract(week from dt), max(amount) from tx group by 1
order by 1;
date_part | max
-----------+--------
31 | 100.00
35 | 200.00
39 | 300.00
40 | 320.00
41 | 400.00
(5 rows)
Instead max() you can use sum().
HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
