cancel
Showing results for 
Search instead for 
Did you mean: 

Group and Sum Query

chris_fawcett
Participant
0 Kudos

Hi Sap Experts

Please can you advise if it is possible to group the below query by Component and sum the Released Qty.   

SELECT T0.[Father] as 'Finished Product', T0.[Code] as 'Component', T1.[OnHand] as 'Stock',  T1.[IsCommited] as 'Required For Production', T1.[OnOrder],(T1.[OnHand] -T1.[IsCommited] + T1.[OnOrder]) as 'Available',

Case

When T3.status = 'r'

Then T2.[PlannedQty] end as 'Released Qty'

FROM ITT1 T0 left join OITW T1 on t0.code=t1.itemcode left join WOR1 T2 on t0.code=t2.itemcode INNER JOIN OWOR T3 ON T2.[DocEntry] = T3.[DocEntry] WHERE T0.[Warehouse] = T1.[WhsCode] and T0.[Father] =[%0]

Many Thanks!!!

Chris 🙂

Accepted Solutions (1)

Accepted Solutions (1)

former_member184146
Active Contributor
0 Kudos

Hi Chris,

try the below query

SELECT distinct T0.[Father] as 'Finished Product', T0.[Code] as 'Component', T1.[OnHand] as 'Stock',  T1.[IsCommited] as 'Required For Production',

T1.[OnOrder],(T1.[OnHand] -T1.[IsCommited] + T1.[OnOrder]) as 'Available',

Case

When T3.status = 'r'

Then sum(T2.[PlannedQty]) end as 'Released Qty'

FROM ITT1 T0 left join OITW T1 on t0.code=t1.itemcode left join

WOR1 T2 on t0.code=t2.itemcode INNER JOIN OWOR T3 ON T2.[DocEntry] = T3.[DocEntry]

WHERE T0.[Warehouse] = T1.[WhsCode] and T0.[Father] =[%0]

  and T3.status = 'r'

group by T0.[Code] ,T0.[Father],T1.[OnHand], T1.[IsCommited],T1.[OnOrder],T3.status

order by T0.[Code]

--Manish

Answers (0)