Column as result of subtraction of two other columns?

On Fri, Jul 16, 2004 at 15:31:33 +0100,
Mark Cave-Ayland <m.cave-ayland@webbased.co.uk> wrote:

Hi everyone,

I'm trying to calculate an output column which is the difference of two
other columns in the query output; the first column is an aggregate of
items in stock, while the second column is an aggregate of items which
have been used. The third column should should be the difference of the
two values so I can then output all three columns in a table.

Unfortunately I can't get this to work at the moment :(. I've simplified
the query down to the following:

dev=# select 1 as a, 2 as b, (b - a) as c;
ERROR: column "b" does not exist
dev=#

Do I need to create some form of alias so the calculation can see the
other columns? I am using PostgreSQL 7.4.2 on Linux.

You can't use column aliases in other columns; you need to repeat the
column expressions.

On 16/07/2004 15:31 Mark Cave-Ayland wrote:

Hi everyone,

I'm trying to calculate an output column which is the difference of two
other columns in the query output; the first column is an aggregate of
items in stock, while the second column is an aggregate of items which
have been used. The third column should should be the difference of the
two values so I can then output all three columns in a table.

Unfortunately I can't get this to work at the moment :(. I've simplified
the query down to the following:

dev=# select 1 as a, 2 as b, (b - a) as c;
ERROR: column "b" does not exist
dev=#

Do I need to create some form of alias so the calculation can see the
other columns? I am using PostgreSQL 7.4.2 on Linux.

I think you can use a sub-select (this works for me on 7.3.4):

select a, b, (b - a) as c from (select .... as a, .... as b from mytable)
as sub;

HTH

-- 
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for 
Business             |
| Computer Consultants         | 
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

Try

select a, b, (b - a) as diff from (
select 1 as a, 2 as b
) as tmp;

John Sidney-Woollett

Bruno Wolff III wrote:

Mark Cave-Ayland wrote:

Hi everyone,

I'm trying to calculate an output column which is the difference of two
other columns in the query output; the first column is an aggregate of
items in stock, while the second column is an aggregate of items which
have been used. The third column should should be the difference of the
two values so I can then output all three columns in a table.

Unfortunately I can't get this to work at the moment :(. I've simplified
the query down to the following:

dev=# select 1 as a, 2 as b, (b - a) as c;
ERROR: column "b" does not exist
dev=#

Do I need to create some form of alias so the calculation can see the
other columns? I am using PostgreSQL 7.4.2 on Linux.

You can can try:

select a, b, a-b from
( select sum( x) as a, sum( y) as b from whatever group by z);

You can also do:

select sum( x), sum( y), sum(x-y) from whatever group by z;

HTH

-----Original Message-----
From: John Sidney-Woollett [mailto:johnsw@wardbrook.com]
Sent: 16 July 2004 16:22
To: Bruno Wolff III
Cc: Mark Cave-Ayland; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Column as result of subtraction of two
other columns?

Try

select a, b, (b - a) as diff from (
select 1 as a, 2 as b
) as tmp;

John Sidney-Woollett

Hi John,

Brilliant - thanks for this! The reason I would like to do it this way
is because in my real database, both a and b are horribly complex with 6
or more joins, and it seems a waste for the database to calculate both
results again just to give the difference. I'll give this a go on Monday
and shout if I still can't get it to work.

Many thanks,

Mark.

---

Mark Cave-Ayland
Webbased Ltd.
Tamar Science Park
Derriford
Plymouth
PL6 8BX
England

Tel: +44 (0)1752 764445
Fax: +44 (0)1752 764446

This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender. You
should not copy it or use it for any purpose nor disclose or distribute
its contents to any other person.

Jean-Luc Lachance <jllachan@sympatico.ca> writes:

Mark Cave-Ayland wrote:

I'm trying to calculate an output column which is the difference of two
other columns in the query output; the first column is an aggregate of
items in stock, while the second column is an aggregate of items which
have been used.

You can also do:
select sum( x), sum( y), sum(x-y) from whatever group by z;

Mark would actually be best off to do this in the straightforward
fashion and not try to be cute about it:

select sum(x), sum(y), sum(x)-sum(y) from ...

At least since 7.4, the system will notice the duplicate aggregates
and run only two summations to compute the above, followed by a single
subtraction at the end. The apparently more intelligent way suggested
by Jean will have to run three summations, and thus end up being a net
loss.

The various subselect notations mentioned elsewhere in the thread may
save a bit of typing, if your column calculations are hairy expressions
and not just "sum(foo)", but they probably won't save any runtime.

regards, tom lane

Tom Lane wrote:

Jean-Luc Lachance <jllachan@sympatico.ca> writes:

Mark Cave-Ayland wrote:

I'm trying to calculate an output column which is the difference of two
other columns in the query output; the first column is an aggregate of
items in stock, while the second column is an aggregate of items which
have been used.

You can also do:
select sum( x), sum( y), sum(x-y) from whatever group by z;

Mark would actually be best off to do this in the straightforward
fashion and not try to be cute about it:

select sum(x), sum(y), sum(x)-sum(y) from ...

At least since 7.4, the system will notice the duplicate aggregates
and run only two summations to compute the above, followed by a single
subtraction at the end. The apparently more intelligent way suggested
by Jean will have to run three summations, and thus end up being a net
loss.

That is indeed new. Nice to know.

On Fri, 16 Jul 2004 12:04:54 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:

select sum(x), sum(y), sum(x)-sum(y) from ...

At least since 7.4, the system will notice the duplicate aggregates
and run only two summations to compute the above, followed by a single
subtraction at the end. The apparently more intelligent way suggested
by Jean will have to run three summations, and thus end up being a net
loss.

Also note that Jean-Luc's
select sum( x), sum( y), sum(x-y) from whatever group by z;
gives a different result in the presence of NULLs.

Servus
Manfred