Thread: getting maximum entry from a sum()

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
date. I start out with this query:

economy=> select dt,sum(amount) as asum from transactions group by dt
order by asum;

   As expected, this will yield a list of all dates I have wasted my
money, and how much I wasted for those dates. Now let's day I only
wanted the maximum amount I spent and what date that was.

   Obviously I could "order by asum" and "limit 1", but this would only
get a single date. What if I want *all* dates which have the same
maximum asum?

   Essentially, I want:

select dt,sum(amount) as asum where asum=(select max(asum) ...) group by dt

   But I can't seem to understand how to formulate such a query.. I've
been trying off and on for a few days now, and I'm only getting more and
more frustrated by it.

   Any hints?

--
Kind Regards,
Jan Danielsson
Te audire non possum. Musa sapientum fixa est in aure.

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

Jan Danielsson <jan.danielsson@gmail.com> writes:
>    Essentially, I want:

> select dt,sum(amount) as asum where asum=(select max(asum) ...) group by dt

There are a couple ways you could do it:

* HAVING clause:

select dt,sum(amount) as asum
  group by dt
  having sum(amount) = (select max(asum) ...)

* ORDER BY/LIMIT:

select dt,sum(amount) as asum
  group by dt
  order by asum desc
  limit 1

The first is standard SQL, the second isn't (no LIMIT in the spec)
but the second is probably more efficient.

            regards, tom lane