Column 'Products.ProductName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Question

I am receiving the following error:

Column 'Products.ProductName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Select  P.ProductName , C.City , Sum(OD.Quantity )as TotalSold,
    DENSE_RANK() OVER ( PARTITION BY C.CITY  ORDER BY OD.Quantity  Desc) as rank
from Products P
Inner Join  [Order Details] OD on P.ProductID = OD.ProductID
Inner Join Orders O on O.OrderID = OD.OrderID
Inner Join Customers C on C.CustomerID = O.CustomerID
where country = 'USA'

But if use the below query, it gives me the correct data. whats the difference between the two if I'm joining all 4 tables in different order?

select p.productName, c.city, od.quantity,
    DENSE_RANK () OVER ( Partition by C.city Order by od.quantity Desc) as rank
from customers C
Inner Join Orders O on C.customerID = O.CustomerID
Inner Join [Order Details] od on O.OrderID = OD.OrderID
Inner Join Products P on OD.ProductID = P.ProductID
where country = 'USA'

But I use the following query, ti gives me the correct records. can anyone pls share the difference between the two queries? difference is only the order of joins select p.productName, c.city, od.quantity, DENSE_RANK () OVER ( Partition by C.city Order by od.quantity Desc) as rank from customers C Inner Join Orders O on C.customerID = O.CustomerID Inner Join [Order Details] od on O.OrderID = OD.OrderID Inner Join Products P on OD.ProductID = P.ProductID where country = 'USA' — Kevin
Please read the official docs they are very clear about how to use group by. — Dale K
Dale K- Thanks I edited the question. can u pls tell me the difference between the 2 queries? latter one gives me the correct records but I do not know why — Kevin
The first query uses Sum(OD.Quantity) i.e. you are trying to aggregate, to aggregate you need to group by something else there is nothing to aggregate. As I said above, read the docs, they are very clear on how to use group by. — Dale K
Where is your GROUP BY? Whatabiout the error don't you understand? — Larnu

GMB GMB · Accepted Answer · 2020-03-30T21:46:55

The two queries do not (attempt to) do the same thing.

The working query (the second one) joins and uses a window function to sort rows having the same city by descending quantity).

The second query is, as far as concerns, an attempt to group the rows productName and city, and to sort the products in each city by their total sales. To make that query valid, you need to add a GROUP BY clause and to change the ORDER BY of the window function:

select  P.ProductName , C.City , Sum(OD.Quantity) as TotalSold,
    DENSE_RANK() OVER ( PARTITION BY C.CITY  ORDER BY Sum(OD.Quantity) Desc) as rank
from Products P
inner Join  [Order Details] OD on P.ProductID = OD.ProductID
inner Join Orders O on O.OrderID = OD.OrderID
inner Join Customers C on C.CustomerID = O.CustomerID
where country = 'USA'
group by P.ProductName, C.City

Column 'Products.ProductName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

1 Answers