Thread: 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.
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: > 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. > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
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 >
> -----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. > 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 >
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