Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jean-Luc Lachance <jllachan(at)sympatico(dot)ca>
Cc: Mark Cave-Ayland <m(dot)cave-ayland(at)webbased(dot)co(dot)uk>,pgsql-general(at)postgresql(dot)org
Subject: Re: Column as result of subtraction of two other columns?
Date: 2004-07-16 16:04:54
Message-ID: 21439.1089993894@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
Jean-Luc Lachance <jllachan(at)sympatico(dot)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

In response to

Responses

pgsql-general by date

Next:From: Jean-Luc LachanceDate: 2004-07-16 17:01:00
Subject: Re: Column as result of subtraction of two other columns?
Previous:From: Mark Cave-AylandDate: 2004-07-16 15:36:24
Subject: Re: Column as result of subtraction of two other columns?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group