Thread: sum an alias
In the statement: select MAX(page_count_count) - MIN(page_count_count) as day_tot, MAX(page_count_count) as day_max, sum(MAX(page_count_count)- MIN(page_count_count)) as tot, page_count_pdate from page_count group by page_count_pdate order by page_count_pdate Is there a way to do sum(day_tot) also in the same statement? thx, -wes
In response to Wes James : > In the statement: > > select > MAX(page_count_count) - MIN(page_count_count) as day_tot, > MAX(page_count_count) as day_max, sum(MAX(page_count_count) - > MIN(page_count_count)) as tot, > page_count_pdate > from page_count > group by page_count_pdate order by page_count_pdate > > Is there a way to do sum(day_tot) also in the same statement? Is this the correct table-definition? test=# \d page_count Table "public.page_count" Column | Type | Modifiers ------------------+---------+-----------page_count_pdate | date |page_count_count | integer | If yes, your SQL is wrong: test=# select MAX(page_count_count) - MIN(page_count_count) as day_tot, MAX(page_count_count) as day_max, sum(MAX(page_count_count)- MIN(page_count_count)) as tot, page_count_pdate from page_count group by page_count_pdate order by page_count_pdate; ERROR: aggregate function calls cannot be nested LINE 3: MAX(page_count_count) as day_max, sum(MAX(page_count_cou... Can you provide the correct table definition? Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
On Thu, Jun 3, 2010 at 11:54 PM, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote: > In response to Wes James : >> In the statement: >> >> select >> MAX(page_count_count) - MIN(page_count_count) as day_tot, >> MAX(page_count_count) as day_max, sum(MAX(page_count_count) - >> MIN(page_count_count)) as tot, >> page_count_pdate >> from page_count >> group by page_count_pdate order by page_count_pdate >> >> Is there a way to do sum(day_tot) also in the same statement? > > Is this the correct table-definition? > > test=# \d page_count > Table "public.page_count" > Column | Type | Modifiers > ------------------+---------+----------- > page_count_pdate | date | > page_count_count | integer | That is the correct table def. > If yes, your SQL is wrong: I know the sql is wrong - I should have mentioned that, but I'm looking for something that will work like that. I need to sum the difference of max() - min((). How can this be done, since there doesn't seem to be a way to sum aggregates. > > test=# select > MAX(page_count_count) - MIN(page_count_count) as day_tot, > MAX(page_count_count) as day_max, sum(MAX(page_count_count) - > MIN(page_count_count)) as tot, > page_count_pdate > from page_count > group by page_count_pdate order by page_count_pdate; > ERROR: aggregate function calls cannot be nested > LINE 3: MAX(page_count_count) as day_max, sum(MAX(page_count_cou... > > > Can you provide the correct table definition? thx, -wes
----- Original Message ----- From: "Wes James" <comptekki@gmail.com> To: <pgsql-sql@postgresql.org> Sent: Friday, June 04, 2010 2:30 PM Subject: Re: [SQL] sum an alias On Thu, Jun 3, 2010 at 11:54 PM, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote: > In response to Wes James : >> In the statement: >> >> select >> MAX(page_count_count) - MIN(page_count_count) as day_tot, >> MAX(page_count_count) as day_max, sum(MAX(page_count_count) - >> MIN(page_count_count)) as tot, >> page_count_pdate >> from page_count >> group by page_count_pdate order by page_count_pdate >> >> Is there a way to do sum(day_tot) also in the same statement? > You can use a nested SELECT. Is there some reason preventing you from doing that? Why don't you do something like SELECT SUM(day_tot) FROM ( select MAX(page_count_count) - MIN(page_count_count) as day_tot,MAX(page_count_count) as day_max,page_count_pdate from page_countgroup by page_count_pdate order by page_count_pdate ); Maybe I 'm misunderstanding the background of what you want to do Best, Oliveiros