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

Question

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?

Answer 1

Like so:

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

Answer 2

You could reuse the query like this:

WITH 
  TURNOVER AS (
    SELECT SUM(...) FROM ...)
  ),
  COST AS(
    SELECT SUM(...) FROM ...
  )

SELECT *
FROM(
 SELECT
   TURNOVER.sum as SUM_TURNOVER
 FROM
 TURNOVER,COST
 WHERE ....
) AS a

This is equivalent to :

SELECT *
FROM(
 SELECT
   TURNOVER.sum as SUM_TURNOVER
 FROM
 (
   SELECT SUM(...) FROM ...)
 )AS TURNOVER,
 (
   SELECT SUM(...) FROM ...
 )AS COST
 WHERE ....
) AS a

There is a point to note here. The first method is more readable and reusable, but the second method might be faster, because the DB might choose a better plan for it.

Answer 3

Perhaps the sql "with" clause could help, as presented here http://orafaq.com/node/1879 (other databases such as Postgres do it as well, not just oracle).

Answer 4

SELECT turnover, cost, turnover - cost
FROM
(
SELECT
(SELECT ...) as turnover,
(SELECT ...) as cost
) as Temp

Answer 5

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.

Answer 6

I think the following will work:

SELECT turnover, cost, turnover-cost as profit FROM
   (SELECT 1 AS FAKE_KEY, SUM(a_field) AS TURNOVER FROM some_table) a
INNER JOIN
   (SELECT 1 AS FAKE_KEY, SUM(a_nother_field) AS COST FROM some_other_table) b
USING (FAKE_KEY);

Not tested on animals - you'll be first! :-)

Share and enjoy.

Answer 7

Use a cross apply or outer apply.

SELECT
  Calc1.turnover,
  Calc2.cost,
  Calc3.profit
from
   cross apply ((SELECT SUM(...) as turnover FROM ...)) as Calc1
   cross apply ((SELECT SUM(...) as cost FROM ...)) as Calc2

   /*
     Note there is no from Clause in Calc 3 below.
     This is how you can "stack" formulas like in excel.
     You can return any number of columns, not just one.
   */
   cross apply (select Calc1.turnover - Calc2.cost as profit) as Calc3

Answer 8

this is pretty old but i ran into this problem and saw this post but didnt manage to solve my problem using the given answers so i eventually arrived at this solution :

if your query is :

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

you can turn it into a subquery and then use the fields such as :

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

) as myFields

i'm not entirely sure if this is a bad way of doing things but performance wise it seems okay for me querying over 224,000 records took 1.5 sec not sure if its later on turned into 2x of the same sub query by DB.

Answer 9

You can use user defined variables like this

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

http://dev.mysql.com/doc/refman/5.7/en/user-variables.html