sqldatabasesubquerycaseconditional-aggregation

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


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                  

Solution

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