Column as result of subtraction of two other columns?

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



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

re: 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:[color=blue]
> 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.[/color]

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 majordomo@postgresql.org)

re: Column as result of subtraction of two other columns?

On 16/07/2004 15:31 Mark Cave-Ayland wrote:[color=blue]
> 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.[/color]

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 majordomo@postgresql.org)

re: 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

Bruno Wolff III wrote:
[color=blue]
> On Fri, Jul 16, 2004 at 15:31:33 +0100,
> Mark Cave-Ayland <m.cave-ayland@webbased.co.uk> wrote:
>[color=green]
>>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.[/color]
>
>
> 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 majordomo@postgresql.org)[/color]

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

re: Column as result of subtraction of two other columns?

Mark Cave-Ayland wrote:
[color=blue]
> 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.[/color]

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
[color=blue]
>
> 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.
>
>
>
> ---------------------------(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
>[/color]


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

re: Column as result of subtraction of two other columns?

On Fri, 16 Jul 2004 12:04:54 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:[color=blue]
> 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.[/color]

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