From: | "Andrew Hammond" <andrew(dot)george(dot)hammond(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: reusing AS |
Date: | 2006-07-25 20:40:15 |
Message-ID: | 1153860015.351799.115210@p79g2000cwp.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Otniel Michael | 2006-07-26 01:43:28 | Re: About Div |
Previous Message | Chris Browne | 2006-07-25 20:39:16 | Re: SQL generator |