Thread: 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:

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?
 

Jan,

Try   'sum(amount)' in place of 'asum'

thanks,
cyrus