Skip to Content
0
Former Member
Mar 29, 2011 at 02:58 AM

Syntax Error - Using "Group By" with "Union All"

114 Views

Dear SAP community, I am a newb in need of help.

Just spent the past 2 days searching for answers on how to build this query.

Basically, I just want to group the Item Names (U_ItemName) together and have their respective quantities (U_Cart) added together so the same items display as one total quantity, no duplicates.

I have tried so many combinations of GroupBy and Sum/Count....etc. Nothing is working.

The query:

SELECT T1.[U_ItemName] AS 'Item Name', T1.[U_Cart] AS 'Cartons Available', T0.[Country] AS 'Country', T1.[U_ShlfLife] AS 'Shelf Life' FROM [dbo].[OWHS] T0 inner join [dbo].[@SICAS_EXT_WHS] T1 on T0.WhsCode=T1.U_WhseCode WHERE T0.[Country] = (N'JP' ) and DateDiff(Month, GetDate(),T1.[U_ShlfLife]) <= 6 union all

SELECT T0.[U_ItemName], Sum(T0.[U_Cart]) as Cartons, T1.[Country], null FROM [dbo].[@SICAS_EXT_WHS] T0, OWHS T1 WHERE T1.[Country] = (N'JP' ) and DateDiff(Month, GetDate(),T0.[U_ShlfLife]) >= 6

GROUP BY T0.[U_ItemName], T0.[U_Cart], T1.[Country]

Can anybody advise how to get this working?

Thank you,

Justice Nerenberg