Обсуждение: Select maximum amoung grouped sums
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
Вложения
--- Jan Danielsson <jan.m.danielsson@gmail.com> wrote: > > ---------------------- > select date_part('year', dt) as year, date_part('week', dt) as week, > sum(amount) as asum > from transactions > group by year,week having asum = ( select max(A.weeksum) from ( select sum(amount) as weeksum from transactions group by date_trunc('week', dt) ) A ) > order by year,week; > ---------------------- This should do what you want, but I expect that there is a better answer. Regards, Richard Broersma Jr.
Richard Broersma Jr wrote: > --- Jan Danielsson <jan.m.danielsson@gmail.com> wrote: >> ---------------------- >> select date_part('year', dt) as year, date_part('week', dt) as week, >> sum(amount) as asum >> from transactions >> group by year,week > having asum = ( > select max(A.weeksum) > from > ( > select sum(amount) as weeksum > from transactions > group by date_trunc('week', dt) > ) A > ) >> order by year,week; >> ---------------------- > > > This should do what you want, but I expect that there is a better answer. Hmm... This gives me: ERROR: column "asum" does not exist LINE 5: having asum = ( Any ideas? -- Kind regards, Jan Danielsson
Вложения
Jan Danielsson wrote:
Try 'sum(amount)' in place of 'asum'
thanks,
cyrus
Jan,Richard Broersma Jr wrote:--- Jan Danielsson <jan.m.danielsson@gmail.com> wrote:---------------------- select date_part('year', dt) as year, date_part('week', dt) as week,sum(amount) as asum from transactions group by year,weekhaving asum = ( select max(A.weeksum) from ( select sum(amount) as weeksum from transactions group by date_trunc('week', dt) ) A )order by year,week; ----------------------This should do what you want, but I expect that there is a better answer.Hmm... This gives me: ERROR: column "asum" does not exist LINE 5: having asum = ( Any ideas?
Try 'sum(amount)' in place of 'asum'
thanks,
cyrus