How to shorten or simplify the code in sql

June 13, 2022

**Query 1:**
   
 SELECT ID
          , COUNT(DISTINCT A.order_id) AS order_count
    FROM table_1 B
    JOIN table_2 A
    ON A.order_id = B.order_id
    WHERE 
    AND A.order_status  IN ('PROCESSING', 'COMPLETED')
    and B.fullfilled_cd = 'DELIVERY'
    GROUP BY  ID;
     
     
**Query 2:**


 SELECT ID
          , COUNT(DISTINCT A.order_id) AS order_count
    FROM table_1 B
    JOIN table_2 A
    ON A.order_id = B.order_id
    WHERE 
    AND A.order_status  IN ('PROCESSING', 'COMPLETED')
    and B.fullfilled_cd = 'PURCHASE'
    GROUP BY  ID;

Kindly guide me how to combine these 2 queries into a single query. COUNT_IF is not possible as fullfilled_cd is not a BOOLEAN column. Can we use CASE statement?

>Solution :

Using conditional aggregation:

SELECT ID
,COUNT(DISTINCT CASE WHEN B.fullfilled_cd = 'DELIVERY' THEN A.order_id END) AS order_count_delivery
,COUNT(DISTINCT CASE WHEN B.fullfilled_cd = 'PURCHASE' THEN A.order_id END) AS order_count_purchase
FROM table_1 B
JOIN table_2 A
  ON A.order_id = B.order_id
WHERE A.order_status  IN ('PROCESSING', 'COMPLETED')
  AND B.fullfilled_cd IN ('DELIVERY','PURCHASE')
GROUP BY  ID;