return subquery value into main query

Question

I have the following query:

SELECT * 
FROM item_ledger 
WHERE to_id='7' 
AND (SELECT SUM(qty) AS qty_in 
     FROM item_ledger 
     WHERE from_item_ledger_id='5') < intransit_qty

the query works as expected. From that query, I also want to return qty_in to the main query. How to do that?

Answer 1

The subquery is uncorrelated, so you can simply make it a derived table (moving it to the FORM clause) or make it a CTE:

-- derived table
SELECT il.*, q.qty_in 
FROM item_ledger AS il
  CROSS JOIN
    (SELECT SUM(qty) AS qty_in 
     FROM item_ledger 
     WHERE from_item_ledger_id = '5') AS q
WHERE il.to_id = '7' 
  AND q.qty_in < il.intransit_qty ;

-- common table expression
WITH q AS
    (SELECT SUM(qty) AS qty_in 
     FROM item_ledger 
     WHERE from_item_ledger_id = '5')
SELECT il.*, q.qty_in 
FROM item_ledger AS il
  CROSS JOIN q
WHERE il.to_id = '7' 
  AND q.qty_in < il.intransit_qty ;

You could also use an inline subquery or a LATERAL join (available in 9.3+ versions) but those solutions would be more useful if the subquery were correlated.