Column as result of subtraction of two other columns?

[Mark Cave-Ayland]

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.
Many thanks,

Mark.

---

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

Tel: [phone]
Fax: [phone]
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.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Bruno Wolff III]

On Fri, Jul 16, 2004 at 15:31:33 +0100,
Mark Cave-Ayland <m.***********@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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Paul Thomas]

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 |
+------------------------------+---------------------------------------------+

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[John Sidney-Woollett]

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:

On Fri, Jul 16, 2004 at 15:31:33 +0100,
Mark Cave-Ayland <m.***********@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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Jean-Luc Lachance]

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

Many thanks,

Mark.

---

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

Tel: [phone]
Fax: [phone]
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.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Manfred Koizar]

On Fri, 16 Jul 2004 12:04:54 -0400, Tom Lane <tg*@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org