Total Stock Count Query

[benchristoffels]

Hello,

I am able to retrieve stock counts through the OITW table, but it gives me results for warehouse, rather than a sum total for all warehouses. How do I get the stock count to report back as a sum total of all warehouses stock count combined versus each warehouse individually.

This is my current query:

SELECT T0.[ItemCode], T0.[WhsCode], T1.[Price] "Dealer Net", T0.[AvgPrice] "WH Cost", T0.[OnHand], T0.[IsCommited] "Commited", T3.[ItmsGrpCod], T3.[frozenFor] FROM OITW T0 inner join ITM1 T1 on (T0.ItemCode = T1.ItemCode and T1.[PriceList] =5) inner join OITM T3 on T0.ItemCode = T3.ItemCode INNER JOIN OWHS T4 ON T0.[WhsCode] = T4.[WhsCode] WHERE T3.[frozenFor] = 'n'

Thanks,

Ben

[former_member185682 · Accepted solution]

Hi Ben,

Try this:

SELECT 
	T0.[ItemCode], 
	AVG(T1.[Price]) "Dealer Net", 
	AVG(T0.[AvgPrice]) "Cost", 
	SUM(T0.[OnHand]), 
	T0.[IsCommited] "Commited", 
	T3.[ItmsGrpCod], 
	T3.[frozenFor] 
FROM 
	OITW T0 
	INNER JOIN ITM1 T1 ON (T0.ItemCode = T1.ItemCode AND T1.[PriceList] =5) 
	INNER JOIN OITM T3 ON T0.ItemCode = T3.ItemCode 
	INNER JOIN OWHS T4 ON T0.[WhsCode] = T4.[WhsCode] 
WHERE 
	T3.[frozenFor] = 'N'
	AND T0.[OnHand] > 0
GROUP BY 
	T0.[ItemCode], T0.[IsCommited], T3.[ItmsGrpCod], T3.[frozenFor]

Hope it helps.

Kind Regards,

Diego Lother

[benchristoffels]

Thanks! Perfect. Appreciate the response