Alex Rivera | Logout

How to reuse a result column in an expression for another result column

Asked 2010-11-18T23:09:10.663
63

Example:

SELECT
   (SELECT SUM(...) FROM ...) as turnover,
   (SELECT SUM(...) FROM ...) as cost,
   turnover - cost as profit

Sure this is invalid (at least in Postgres) but how to achieve the same in a query without rewriting the sub-query twice?

sql postgresql subquery
Edit
Report

1 Answer

4

Actually I did a lot of work on this, and hit many brick walls, but finally figured out an answer - more of a hack - but it worked very well and reduced the read overhead of my queries by 90%....

So rather than duplicating the correlated query many times to retrieve multiple columns from the subquery, I just used concat all the values I want to return into a comma separated varchar, and then unroll them again in the application...

So instead of

select a,b,
(select x from bigcorrelatedsubquery) as x,
(select y from bigcorrelatedsubquery) as y,
(select z from bigcorrelatedsubquery) as z
from outertable

I now do

select a,b,
(select convert(varchar,x)+','+convert(varchar,x)+','+convert(varchar,x)+',' 
from bigcorrelatedsubquery) from bigcorrelatedquery) as xyz
from outertable
group by country

I now have all three correlated 'scalar' values I needed but only had to execute the correlated subquery once instead of three times.

answered 2011-07-21T12:53:20.437

Your Answer