Обсуждение: 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