CODEWITHSUNDEEP
sqlsql-server
UltraJ
UltraJ

Reputation: 487

How to Fix "Column is invalid because its not contained in aggregate function or GROUP By clause"

I'm trying to extract data from two tables that counts the number of orders that each client has placed. The SQL below works fine without the S.Name in the SELECT, but I would like the Name of the client in the output which is more meaningful than their StoreID. When I add S.Name, I get the following error:

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

SELECT O.StoreID, COUNT(O.StoreID) AS OrderCount, S.Name
FROM Orders AS O
JOIN Stores AS ON O.StoreID = S.StoreID
GROUP BY O.StoreID
ORDER BY OrderCount DESC

How do I make this query work?

Upvotes: 0

Views: 3257

Answers (2)

Bohemian
Bohemian

Reputation: 425033

You have to include all non-aggregated columns in the group by list; you've selected S.Name, which is not an aggregate but not in the GROUP BY:

SELECT O.StoreID, COUNT(O.StoreID) AS OrderCount, S.Name
FROM Orders AS O
JOIN Stores AS S ON O.StoreID = S.StoreID
GROUP BY O.StoreID, S.Name -- Added "S.Name" to the GROUP BY list
ORDER BY OrderCount DESC

Soap box time: The whole concept of the group by clause is useless as it is completely deterministic - ie there is only one valid group by expression for a given select list (ignoring functionally identical permutations of the list), so why is it required? Why doesn't the query parser just assume it - it checks to see if it's correct, but it can only be one thing, so it's doing all the work required to generate it itself already.

It is ironic that this fact about the language essentially breaks 3NF - ie the group by clause is redundant data in the query itself (it can be determined from other parts of the query). From a data perspective, the language would be considered "badly designed" if measured by its own standards.

Upvotes: 6

I A Khan
I A Khan

Reputation: 8839

SELECT
  O.StoreID,
  COUNT(O.StoreID) AS OrderCount,
  S.Name
FROM Orders AS O
JOIN Stores AS S
  ON O.StoreID = S.StoreID
GROUP BY O.StoreID,
         S.Name
ORDER BY OrderCount DESC

When you use count, or sum etc. function then should be group by all fields, in your case O.StoreID, S.Name for more information

http://www.techonthenet.com/sql_server/group_by.php

https://msdn.microsoft.com/en-us/library/ms177673.aspx

Upvotes: 0

Related Questions