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