Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 584
Wernight Avatar asked Nov 18 '10 23:11

Wernight


People also ask

How do you use the result of a query in another query?

Use the results of a query as a field in another query. You can use a subquery as a field alias. Use a subquery as a field alias when you want to use the subquery results as a field in your main query. Note: A subquery that you use as a field alias cannot return more than one field.


1 Answers

Like so:

SELECT    turnover,    cost,    turnover - cost as profit from (    (SELECT SUM(...) FROM ...) as turnover,    (SELECT SUM(...) FROM ...) as cost    ) as partial_sums 
like image 142
Denis de Bernardy Avatar answered Sep 28 '22 17:09

Denis de Bernardy