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
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)