Re: [SQL] how to construct sql
On 2010-06-02, Wes James wrote: > On Wed, Jun 2, 2010 at 10:55 AM, Oliveiros > wrote: >> Hi, >> Have you already tried this out? >> >> select MAX(page_count_count) - MIN(page_count_count) from page_count group >> by page_count_pdate. >> >> >> Best, >> Oliveiros > > Oliveiros, > > Thx that mostly works. I just tried it and on the days there is only > 1 entry it is 0 since max is the same as min so max - min is 0. Is > there a way to take in to account the 1 entry days? from your original requirement 0 is the correct answer. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] sum an alias
On Thu, Jun 3, 2010 at 11:54 PM, A. Kretschmer 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] sum an alias
- Original Message - From: "Wes James" To: 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 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_count group by page_count_pdate order by page_count_pdate ); Maybe I 'm misunderstanding the background of what you want to do Best, Oliveiros -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
