SUMMARY: Never use fields in the GROUP BY that aren't part of the grouping. Read on...
I don't know how many times I have seen the following SQL, but it's not always accurate, and most irritating of all it is just wrong.
SELECT C.CustomerID, CustomerName, City, State, Sum(Sales) as TotalSales
FROM Customers C
INNER JOIN Sales S on C.CustomerID = Sales.CustomerID
GROUP BY CustomerID, CustomerName, City, State
The reason developers write a query like this in the first place is they see the common error message that says, "Column X is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." The only way they know how to solve this problem is to start adding columns to the the GROUP BY until the query runs. This is where having an understanding of Set Theory comes in handy.
The query should be written thusly:
SELECT C.CustomerID, C.CustomerName, C.City, C.State, S.TotalSales
FROM Customers C
INNER JOIN
(SELECT CustomerID, SUM(Sales) as TotalSales
FROM Sales
GROUP BY CustomerID) S
ON C.CustomerID = S.CustomerID
Essentially what this does is it gets the TotalSales from the sales table (that explicity all I want and need to get the total sales). Then in order to pick up the other data that I need to produce my report, I join on the key field for CustomerID. Not only is this always going to be correct, it is also the correct way to do the GROUP BY.
Print | posted on Sunday, March 11, 2007 2:08 AM