Select maximum amoung grouped sums
От | Jan Danielsson |
---|---|
Тема | Select maximum amoung grouped sums |
Дата | |
Msg-id | 45C9F0C3.7080508@gmail.com обсуждение исходный текст |
Ответы |
Re: Select maximum amoung grouped sums
|
Список | 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
Вложения
В списке pgsql-novice по дате отправления: