sql server group by clause error

Question

I keep getting the aggregate group by clause error on the below query. I'm new to SQL and can't quite figure it out. I keep getting duplicate records due to the JDEppsin table and a distinct clause won't do it either.

SELECT 
      JDEWorkOrderIn_LastImport.WorkOrderNumber,
      JDEWorkOrderIn_LastImport.Quantity,
      JDEWorkOrderIn_LastImport.LineNumber,  
      JDEWorkOrderIn_LastImport.SKU,
      JDEWorkOrderIn_LastImport.SKUDesc1,
      JDEWorkOrderIn_LastImport.SKUDesc2,
      arc_JDEPPSIn.PalletSheet,
      arc_JDEPPSIn.PalletConfigDesc,
      arc_JDEPPSIn.LabelAlcVol,
      arc_JDEPPSIn.CartonInkjetCode,
      arc_JDEPPSIn.SpecialInstructions,
      JDEWorkOrderIn_LastImport.WAWR02
      FROM [WBPC_MES_Integration].[dbo].[arc_JDEPPSIn]
join [WBPC_MES_Integration].[dbo].[JDEWorkOrderIn_LastImport] on arc_JDEPPSIn.WorkOrderNumber = JDEWorkOrderIn_LastImport.WorkOrderNumber
where JDEWorkOrderIn_LastImport.startdate between getdate()-7 and getdate()+5
group by arc_JDEPPSIn.workordernumber 

I googled about using a virtual table to group it this is what I've come up with but I just dont know how to join I'm self taught so trying to figure it out

Answer 1

Whenever you use a GROUP clause all non-aggregate values (not SUM, COUNT, AVG etc...) in your SELECT statement need to appear in your group by statement. Try adding:

GROUP BY
JDEWorkOrderIn_LastImport.WorkOrderNumber,
JDEWorkOrderIn_LastImport.Quantity,
JDEWorkOrderIn_LastImport.LineNumber,  
JDEWorkOrderIn_LastImport.SKU,
JDEWorkOrderIn_LastImport.SKUDesc1,
JDEWorkOrderIn_LastImport.SKUDesc2,
arc_JDEPPSIn.PalletSheet,
arc_JDEPPSIn.PalletConfigDesc,
arc_JDEPPSIn.LabelAlcVol,
arc_JDEPPSIn.CartonInkjetCode,
arc_JDEPPSIn.SpecialInstructions,
JDEWorkOrderIn_LastImport.WAWR02

With that said, if you do not have an aggregate value you shouldn't need to group. I would use SELECT DISTINCT instead if you are trying to remove duplicates. If you are not, then just remove the group by.