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

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