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

sql postgresql subquery
Wernight picture Wernight · Nov 19, 2010 · Viewed 85.5k times · Source

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

Denis de Bernardy picture Denis de Bernardy · Jul 21, 2011

Like so:

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

Related questions

updating table rows in postgres using subquery

Using postgres 8.4, My goal is to update existing table: CREATE TABLE public.dummy ( address_id SERIAL, addr1 character(40), addr2 character(40), city character(25), state character(2), zip character(5), customer boolean, supplier boolean, partner boolean ) WITH ( OIDS=FALSE ); Initially i tested my query …

Read More
How to do a Postgresql subquery in select clause with join in from clause like SQL Server?

I am trying to write the following query on postgresql: select name, author_id, count(1), (select count(1) from names as n2 where n2.id = n1.id and t2.author_id = t1.author_id ) from names as n1 group by name, …

Read More
subquery in FROM must have an alias

I have this query I have written in PostgreSQL that returns an error saying: [Err] ERROR: LINE 3: FROM (SELECT DISTINCT (identifiant) AS made_only_recharge This is the whole query: SELECT COUNT (made_only_recharge) AS made_only_recharge FROM ( …

Read More