SQL Server MVP Deep Dives- P5: Each year Microsoft invites all the MVPs from every technology and country to Redmond for an MVP Summit—all top secret—“don’t tweet what you see!” During the MVP Summit, each product team holds a series of presentations where they explain their technologies, share their vision, and listen to some honest feedback. | 116 Chapter 9 Avoiding three common query mistakes Incorrect GROUP BY clauses Figuring out which columns belong in the GROUP BY clause in an aggregate query often aggravates T-SQL developers. The rule is that any column that is not part of an aggregate expression in the SELECT or ORDER BY clauses must be listed in the GROUP BY clause. That rule seems pretty simple but I have seen many questions on forums about this very point. If a required column is missing from the GROUP BY clause you will not get incorrect results you will get no results at all except for an error message. If extra columns are listed in the GROUP BY clause no warning message will appear but the results will probably not be what you intended. The results will be grouped at a more granular level than expected. I have even seen code that incorrectly included the aggregated column in the GROUP BY clause. The query in listing 10 is missing the GROUP BY clause. Listing 10 Missing the GROUP BY clause SELECT COUNT CustomerID FROM Msg 8120 Level 16 State 1 Line 1 Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Listing 11 contains a query that lists the count of orders by CustomerID. The query includes the order date in the GROUP BY clause so that the results do not make sense. Figure 7 shows that there are multiple rows for each CustomerID value. Listing 11 An extra column in the GROUP BY clause SELECT COUNT AS CountOfOrders CustomerID FROM GROUP BY CustomerID OrderDate ORDER BY CustomerID Another issue to watch out for is including only the column in the GROUP BY clause when the column is used in an expression in the SELECT list. Say you want the results grouped by the year in which the orders were placed. If you leave the order date out of the GROUP BY clause an error will result. If you add the column the error goes away but the results are not .
