Re: getting maximum entry from a sum()

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: getting maximum entry from a sum()
Date: 2006-10-06 14:21:24
Message-ID: 20061006142124.GA5282@a-kretschmer.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2006-10-06 14:49:42 Re: getting maximum entry from a sum()
Previous Message Jan Danielsson 2006-10-06 13:39:14 getting maximum entry from a sum()