| From: | Jan Danielsson <jan(dot)m(dot)danielsson(at)gmail(dot)com> |
|---|---|
| To: | pgsql-novice(at)postgresql(dot)org |
| Subject: | Select maximum amoung grouped sums |
| Date: | 2007-02-07 15:31:15 |
| Message-ID: | 45C9F0C3.7080508@gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-novice |
Hello all,
I have a table containing transaction entries. These contain
transaction dates, and how much was transferred. I want to find out
which week(s) have had the maximum total transfer, and how much was
transferred then. I know how to group the entries:
----------------------
select date_part('year', dt) as year, date_part('week', dt) as week,
sum(amount) as asum
from transactions
group by year,week
order by year,week;
----------------------
But what I want now is to find the *maximum* (or minimum, for that
matter) entry only.
One way would be to simply order the entries by asum appropriately,
and grab the first entry. But I don't want to use that solution for two
reasons:
1) I've been trying to use the MAX() function, without success. I
kind of understand why it doesn't work (it complains about aggregate
functions), but I would like to understand how it should be solved using
MAX()
2) In the (very unlikely) case there are *two* weeks with the exact
same sum I want be able to get both of them).
I assume that the solution is something along the lines of: "Get list
of sums grouped by week where the grouped sum equals the maximum of the
grouped sums". But I can't seem to formulate that in SQL.
--
Kind regards,
Jan Danielsson
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Richard Broersma Jr | 2007-02-07 16:09:09 | Re: Select maximum amoung grouped sums |
| Previous Message | Richard Broersma Jr | 2007-02-06 18:50:02 | Re: Fill multiple fields through one INNER JOIN |