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

Tom Lane wrote:

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

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
>