DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:content-type:content-transfer-encoding;
	b=ZL+HN4NqfakmrVC5YnZc1EJJWK2NAT2NXnZl5ikIv6nZrNkve81bq3NuVsISJi3Rad
	aP8+Scmt+hKE7yyM4MdGkVJpi4Pxb35Yh+D4ypEQ9Yom+G+CqkpvHNjjozjcwXIFFPOG
	QDfW24ZXM+mZsRQnwobWTGQxc4bGBo5lDxNO0=
MIME-Version: 1.0
In-Reply-To: <20100604055434.GA19411@a-kretschmer.de>
References: <AANLkTil0i1cg1DulN4sIEIa08qM5VU5VuLoKB7u8g1-D@mail.gmail.com>
	<20100604055434.GA19411@a-kretschmer.de>
Date: Fri, 4 Jun 2010 07:30:18 -0600
Message-ID: <AANLkTilYVcqa-2exe48IhvV-HSEcWKFSAO1B03hsoBtG@mail.gmail.com>
Subject: Re: sum an alias
From: Wes James <comptekki@gmail.com>
To: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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
>> =A0 =A0 MAX(page_count_count) - MIN(page_count_count) as day_tot,
>> =A0 =A0 MAX(page_count_count) as day_max, sum(MAX(page_count_count) -
>> MIN(page_count_count)) as tot,
>> =A0 =A0 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=3D# \d page_count
> =A0 =A0 =A0 Table "public.page_count"
> =A0 =A0 =A0Column =A0 =A0 =A0| =A0Type =A0 | Modifiers
> ------------------+---------+-----------
> =A0page_count_pdate | date =A0 =A0|
> =A0page_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=3D# select
> =A0 =A0MAX(page_count_count) - MIN(page_count_count) as day_tot,
> =A0 =A0MAX(page_count_count) as day_max, sum(MAX(page_count_count) -
> MIN(page_count_count)) as tot,
> =A0 =A0page_count_pdate
> from page_count
> group by page_count_pdate order by page_count_pdate;
> ERROR: =A0aggregate function calls cannot be nested
> LINE 3: =A0 =A0 MAX(page_count_count) as day_max, sum(MAX(page_count_cou.=
..
>
>
> Can you provide the correct table definition?


thx,

-wes