Skip to Content

Total Stock Count Query

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Mar 03, 2017 at 07:21 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 03, 2017 at 07:48 PM

    Thanks! Perfect. Appreciate the response

    Add comment
    10|10000 characters needed characters exceeded