Re: reusing AS

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