GROUP BY techniques (SQL Server)

Send Us a Sign! (Contact Us!)

--> (Word)	--> (PDF)	--> (Epub)	--> (Text)
--> (XML)	--> (OpenOffice)	--> (XPS)	--> (MHT)

Show More Info

How many times this articles has been read? 35

This article has 445 total words. (Including header)

One aspect of the versatile SELECT statement that seems to confuse many people is the GROUP BY clause.  It is very important to group your rows in the proper place.

Always push GROUP BY aggregations as far into your nested SELECT statements as possible – if you have a situation in which you are grouping by long lists of columns that are not part of primary keys, you are probably have not structured your query correctly.

Here's a classic example that returns total sales per customer, in addition to returning the customer's name and address:

SELECT

  C.CustomerID, C.CustomerName,

  C.CustomerType, C.Address1, C.City,

  C.State, SUM(S.Sales) as TotalSales

FROM

  Customers C

INNER JOIN Sales S

  ON C.CustomerID = S.CustomerID

GROUP BY

  C.CustomerID, C.CustomerName,

  C.CustomerType, C.Address1, C.City, C.State

I can’t say how many times I see SELECT's written this way and it is simply wrong. You should only be grouping on CustomerID, and not on all those other columns.  Push the grouping down a level, into a derived [gs table]:

SELECT

  C.CustomerID, C.CustomerName,

  C.CustomerType, C.Address1, 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

Hopefully, you will agree that it is a much cleaner SELECT statement now, it is more efficient and it logically makes more sense when you look at it.

One more common mistake is that people just mimic the expressions in their SELECT list in the GROUP BY clause, without thinking logically about what grouping is really necessary.  For example:

SELECT LastName + ', ' + FirstName, ... etc ...

FROM Names

GROUP BY LastName + ', ' + FirstName

Again, that is logically wrong and also less efficient.  You should not be grouping by the expression itself; you should be grouping by what is needed to evaluate that expression. The correct grouping is:

GROUP BY LastName, FirstName

Too many people just keep stuffing column names and expressions into the GROUP BY clause until the errors go away without stepping back and thinking logically about how grouping works.

Take a minute to really consider what you need to return and how it should be grouped, and try using derived tables more frequently when writing aggregate queries to help keep them structured and efficient.

SOURCE	LINK	LANGUAGE	ENGLISH