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

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