Wernight Wernight - 4 months ago 16
SQL Question

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

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

Like so:

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