none
Group By query Error RRS feed

  • Question

  • SELECT Year(glpostdt) as Year, Item, Quantity, Sales, (Sales/Quantity) as ASP 
      FROM ABC
      Where Seller like ('XYZ%')
      group By Item
      order by year, Item

    Msg 8120, Level 16, State 1, Line 2
    Column 'XYX' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    Friday, December 6, 2019 5:34 PM

All replies

  • The query you posted cannot cause the error you included. Probably your attempt to obfuscate has added errors into the query. I can say that in a query like this you must group by all columns that are included in the select list that are not aggregated. But your query has no aggregates, so it is impossible to offer useful suggestions. My guess is that you intended (or should) sum the columns Quantity and Sales and then divide the summed values to produce ASP. But that is just a guess. 
    Friday, December 6, 2019 5:44 PM
  • SELECT Year(glpostdt) AS Year, Item, SUM(Quantity) AS Quantity, SUM(Sales) AS Sales, (SUM(Sales)/SUM(Quantity)) AS ASP
    FROM ABC
    WHERE Seller like ('XYZ%')
    GROUP BY Item
    ORDER BY year, Item

    A Fan of SSIS, SSRS and SSAS

    Friday, December 6, 2019 5:47 PM