Re: getting maximum entry from a sum()

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