Combine and SUM query

[BAH_OH]

Hello,

I am struggling to get the correct formatting to be able to display the sum correctly. I have all the data that I need from the following query; however, instead of showing (T4."OnHand"), (T4."IsCommited"),and (T4."OnOrder") in the table, I want to sum those fields and display only the result as "Available QTY".

SELECT T0."ItemCode", T0."ItemName", T0."ItmsGrpCod", T3."ItmsGrpNam", T1."PriceList", T2."ListName", T1."Price", T4."OnHand", T4."IsCommited", T4."OnOrder" FROM OITM T0 INNER JOIN ITM1 T1 ON T0."ItemCode" = T1."ItemCode" INNER JOIN OPLN T2 ON T1."PriceList" = T2."ListNum" INNER JOIN OITB T3 ON T0."ItmsGrpCod" = T3."ItmsGrpCod" INNER JOIN OITW T4 ON T0."ItemCode" = T4."ItemCode" WHERE T0."ItmsGrpCod" = 126 AND T2."ListNum" = 3

I tried some modifications using UNION ALL and then SELECT(sum(T4."OnHand")-(T4."IsCommited")+(T4."OnOrder"))As"Available QTY"FROM OITW T4 but I', not getting what I need. Any assistance in the correct formatting would be much appreciated.

[SonTran · Accepted solution]

Hi,

Try this

SELECT T0."ItemCode", T0."ItemName", T0."ItmsGrpCod", T3."ItmsGrpNam", T1."PriceList", 
T2."ListName", T1."Price", sum(T4."OnHand") - sum(T4."IsCommited") + sum(T4."OnOrder") AS "AvailQty"
FROM OITM T0 INNER JOIN ITM1 T1 ON T0."ItemCode" = T1."ItemCode" 
INNER JOIN OPLN T2 ON T1."PriceList" = T2."ListNum" 
INNER JOIN OITB T3 ON T0."ItmsGrpCod" = T3."ItmsGrpCod" 
INNER JOIN OITW T4 ON T0."ItemCode" = T4."ItemCode" 
WHERE T0."ItmsGrpCod" = 126 AND T2."ListNum" = 3
GROUP BY T0."ItemCode", T0."ItemName", T0."ItmsGrpCod", T3."ItmsGrpNam", T1."PriceList", T2."ListName", T1."Price"

Hope this helps,

Son Tran

[BAH_OH · Accepted solution]

Son Tran, you're awesome! That works perfectly, thank you so much!