How could I Group according to Item Number?-CodePudding

I want to erase all duplicates i.[No] has, since it has a one to many relation to other tables or fields. I have tried this code, but it will not let me group by, although, it comes after the where clause. Any ideas how to group by [No_]?

SELECT i.[No_]
      ,i.[Description]
      ,i.[Manufacturer Code]
      ,m.[Name] as [Manufacturer Name]
      ,i.[Second Hand]
      ,i.[Set Item]
      ,FORMAT(p.[Direct Unit Cost], 'N') as [Direct Unit Cost]
      ,CONVERT(VARCHAR, p.[Starting Date], 104) as [Starting Date]
      ,CONVERT(VARCHAR, p.[Ending Date], 104) as [Ending Date]
      ,si.[Saleable]
      ,FORMAT(il.[SumRemQ], 'N') as [SumRemQ]
FROM [0815Onlinehandel_90].[dbo].[0815GMBH$Item] as i
INNER JOIN [0815Onlinehandel_90].[dbo].[0815GMBH$Manufacturer] as m
ON i.[Manufacturer Code] = m.[Code]
INNER JOIN [0815Onlinehandel_90].[dbo].[0815GMBH$Purchase Price] as p
ON i.No_ = p.[Item No_]
INNER JOIN [0815Onlinehandel_90].[dbo].[0815GMBH$Shop Item] as si
ON p.[Item No_] = si.[Item No_]
INNER JOIN (SELECT [Item No_], SUM([Remaining Quantity]) AS [SumRemQ]
    FROM [0815Onlinehandel_90].[dbo].[0815GMBH$Item Ledger Entry]
    GROUP BY [Item No_]) as il
ON si.[Item No_] = il.[Item No_]
WHERE i.[Second Hand] = 0
AND i.[Set Item] = 0
AND si.Saleable = 1
AND ([Starting Date] < '01.01.2022' AND [Ending Date] = '01.01.1753')
AND (i.[Manufacturer Code]  = '631763'
OR i.[Manufacturer Code]  = '631773'
OR i.[Manufacturer Code]  = 'HER000002'
OR i.[Manufacturer Code]  = '631813'
OR i.[Manufacturer Code]  = '18'
OR i.[Manufacturer Code]  = '631917'
OR i.[Manufacturer Code]  = '4'
OR i.[Manufacturer Code]  = 'HER000426')
GROUP BY i.[No_]

CodePudding user response：

Group by i.No would require the final result to have only one row per i.No.

Because of this you need to tell, up front, what you want to happen with potentially different values other selected columns would return. For example, for i.No=1 the SQL processor doesn't know if there are multiple rows in your results and if (as an example) si.Saleable can have different values on different rows, and it wants you to tell it what you want happen. Most of the time you want an 'aggregation' to happen on those; like SUM or AVERAGE them, or take the smallest (MIN(si.Saleable)) or largest etc. Unless you do so, you can't group by i.No.

Therefore, if you want to GROUP BY i.No, decide what you want to do with the other columns in your select list, and either aggregate them using SUM/MIN/MAX/AVG, etc., or add them to the GROUP BY list, as well (in which case you would have one row of result per unique combination of those values in the group by)