From: "Andrew Hammond" <andrew.george.hammond@gmail.com>
Subject: Re: reusing AS
Date: 25 Jul 2006 13:40:15 -0700
Organization: http://groups.google.com
Lines: 38
Message-ID: <1153860015.351799.115210@p79g2000cwp.googlegroups.com>
References: <e9m4sj$u11$1@news.hub.org>
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
In-Reply-To: <e9m4sj$u11$1@news.hub.org>
User-Agent: G2/0.2
Complaints-To: groups-abuse@google.com
Injection-Info: p79g2000cwp.googlegroups.com; posting-host=207.219.45.62;
	posting-account=cSUlcA0AAABWACaPjo2Z9C0GBLTio512
To: pgsql-sql@postgresql.org


Gregory Stewart wrote:
> I am trying to do something like this:
>
> SELECT SUM(sales_today) AS sales_today_total, SUM(sales_lastweek) AS
> sales_lastweek_total
> CASE WHEN sales_today_total = '0' THEN '0'::int4 WHEN sales_lastweek_total =
> '0' THEN '0'::int4 ELSE ((100/sales_today_total*sales_lastweek_total)-100)
> END AS variance
> FROM mytable
>
> I am getting the message that the column 'sales_today_total' and
> 'sales_lastweek_total' do not exist. It looks like I can't reference the
> aliases I defined (sales_today_total, sales_lastweek_total).

That is correct. Use a sub-query:

SELECT sales_today_total, sales_lastweek_total,
     CASE
        WHEN sales_today_total = 0 THEN 0
        WHEN sales_lastweek_total = 0 THEN 0
        ELSE ((100/sales_today_total*sales_lastweek_total)-100)
    END AS variance
FROM (SELECT SUM(sales_today) AS sales_today_total,
                        SUM(sales_lastweek) AS sales_lastweek_total
            FROM yourtable);

> The reason I am asking is my actual SQL query is rather long with a few
> dozen SUM functions, most of them being reused within the query for
> calculations. I just don't want Postgresql to calculate the same thing over
> and over again and getting the same results and wasting resources.

It won't if you
1) Don't use volatile functions (obviously)
2) Don't force recalculation by correlating your sub-queries.

Drew