| From: | Jan Danielsson <jan(dot)danielsson(at)gmail(dot)com> |
|---|---|
| To: | pgsql-novice(at)postgresql(dot)org |
| Subject: | getting maximum entry from a sum() |
| Date: | 2006-10-06 13:39:14 |
| Message-ID: | 45265C82.6040501@gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-novice |
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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | A. Kretschmer | 2006-10-06 14:21:24 | Re: getting maximum entry from a sum() |
| Previous Message | Tomeh, Husam | 2006-10-05 21:00:16 | Re: Interface of the R-tree in order to work with |