Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group