How to reuse a result column in an expression for another result column
SqlPostgresqlSubquerySql Problem Overview
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 Solutions
Solution 1 - Sql
Like so:
SELECT
turnover,
cost,
turnover - cost as profit
from (
(SELECT SUM(...) FROM ...) as turnover,
(SELECT SUM(...) FROM ...) as cost
) as partial_sums
Solution 2 - Sql
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.
Solution 3 - Sql
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).
Solution 4 - Sql
SELECT turnover, cost, turnover - cost
FROM
(
SELECT
(SELECT ...) as turnover,
(SELECT ...) as cost
) as Temp
Solution 5 - Sql
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.
Solution 6 - Sql
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.
Solution 7 - Sql
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
Solution 8 - Sql
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.
Solution 9 - Sql
You can use user defined variables like this
SELECT
@turnover := (SELECT SUM(...) FROM ...),
@cost := (SELECT SUM(...) FROM ...),
@turnover - @cost as profit