Combine 2 columns and display result

Question

Hi

I have below query . I want to display QtyIn-Qty Out and difference should be > 0

SELECT T1."WhsCode",T1."ItemCode",T1."BatchNum",
(Select Sum("Quantity") from IBT1 where "WhsCode" = T1."WhsCode" and "ItemCode" = T1."ItemCode" and "BatchNum" = T1."BatchNum" and "Direction" = 0) As "Qty In",
(Select Sum("Quantity") from IBT1 where "WhsCode" = T1."WhsCode" and "ItemCode" = T1."ItemCode" and "BatchNum" = T1."BatchNum" and "Direction" = 1) As "Qty Out"
FROM IBT1 T1 
Left Join OITM T2 on T2."ItemCode" = T1."ItemCode"
group by T1."WhsCode",T1."ItemCode",T1."BatchNum"

Thanks

Muhammad Imran Ansari · Answer

Hello Ramco To display the difference between Qty In and Qty Out where the difference is greater than 0 you can modify your query to include a HAVING clause. The HAVING clause is used to filter results after the GROUP BY has been applied. Here's how you can adjust your query: SELECT T1.WhsCode T1.ItemCode T1.BatchNum SUM(CASE WHEN T1.Direction = 0 THEN T1.Quantity ELSE 0 END) AS Qty In SUM(CASE WHEN T1.Direction = 1 THEN T1.Quantity ELSE 0 END) AS Qty Out SUM(CASE WHEN T1.Direction = 0 THEN T1.Quantity ELSE 0 END) - SUM(CASE WHEN T1.Direction = 1 THEN T1.Quantity ELSE 0 END) AS Difference FROM IBT1 T1 LEFT JOIN OITM T2 ON T2.ItemCode = T1.ItemCode GROUP BY T1.WhsCode T1.ItemCode T1.BatchNum HAVING SUM(CASE WHEN T1.Direction = 0 THEN T1.Quantity ELSE 0 END) - SUM(CASE WHEN T1.Direction = 1 THEN T1.Quantity ELSE 0 END) > 0;