Is there a way to convert a query contianing multiple subqueries fetching the same column from same table, into one query

Question

Is there a way to convert the following into a query without subqueries (or with a lesser number of subqueries) in order to make it faster.

select (   +(select sum(coalesce(quantity,0)) from transaction where 
            buyer_number = 101 and stock_id = 22 and status = \'bought\') 
           -(select sum(coalesce(quantity,0)) from transaction where 
            seller_number = 101 and stock_id = 22 and status = \'sold\') 
       ) as balance_primary, 
       (   +(select sum(coalesce(quantity,0)) from transaction where 
            buyer_number = 101 and stock_id = 22 and status = \'received\') 
           -(select sum(coalesce(quantity,0)) from transaction where 
            seller_number = 101 and stock_id = 22 and status = \'gifted\') 
       ) as balance_secondary                  

Answer 1

All the WHERE clauses in the subqueries use the condition stock_id = 22, so it can be moved in the WHERE clause of the final query.

You can get the sums with conditional aggregation:

SELECT SUM(CASE WHEN buyer_number = 101 AND status = 'bought' THEN quantity ELSE 0 END) -
       SUM(CASE WHEN seller_number = 101 AND status = 'sold' THEN quantity ELSE 0 END) AS balance_primary,
       SUM(CASE WHEN buyer_number = 101 AND status = 'received' THEN quantity ELSE 0 END) -
       SUM(CASE WHEN seller_number = 101 AND status = 'gifted' THEN quantity ELSE 0 END) AS balance_secondary 
FROM transaction
WHERE stock_id = 22;

You could also write the WHERE clause as:

WHERE stock_id = 22 AND 101 IN (buyer_number, seller_number)