From: Andrew Hammond <andrew.george.hammond@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: reusing AS
Date: 25 Jul 2006 13:40:15 -0700
Message-ID: <1153860015.351799.115210@p79g2000cwp.googlegroups.com> (raw)
In-Reply-To: <e9m4sj$u11$1@news.hub.org>
References: <e9m4sj$u11$1@news.hub.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andrew.george.hammond@gmail.com
Subject: Re: reusing AS
In-Reply-To: <1153860015.351799.115210@p79g2000cwp.googlegroups.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox