Query - Grouping by Item

[Former Member]

Am trying to have same items on a particular day be grouped under one total but even after adding sum it is giving me same items of the same day separate...

SELECT T0.[PostDate],T0.[DocNum],T1.[ItemCode], T1.[ItemName], sum(T0.[PlannedQty]) as 'Planned Qty', sum(T0.[CmpltQty]) 'Comp Qty' FROM OWOR T0 inner join OITM T1 on T1.[ItemCode] =T0.[ItemCode] WHERE T0.[PostDate] >=[%0] and T0.[PostDate] <=[%1] and T1.[ItmsGrpCod] in (104,106,107,108,113,116,120) GROUP BY T0.[PostDate], T1.[ItemCode], T1.[ItemName], T0.[PlannedQty], T0.[CmpltQty],T0.[DocNum]

[bhavank_gajjar · Accepted solution]

Hi..

From Group By Remove Planned Qty & Completed Qty because you have made Sum for those fields in Select Query so remove from them group by you will get your desire.

Regards,

Bhavank

[Former Member · Accepted solution]

Thanks alot!

[former_member204969]

Try to leave out the docnum if you want only the sums:

SELECT T0.PostDate,T1.ItemCode, T1.ItemName,
 sum(T0.PlannedQty) as 'Planned Qty', sum(T0.CmpltQty) 'Comp Qty'
 FROM OWOR T0 inner join OITM T1 on T1.ItemCode =T0.ItemCode
 WHERE T0.PostDate >=[%0] and T0.PostDate <=[%1] and T1.ItmsGrpCod in (104,106,107,108,113,116,120)
 GROUP BY T0.PostDate, T1.ItemCode, T1.ItemName, T0.PlannedQty, T0.CmpltQty