HEELPBOOK - GROUP BY techniques (SQL Server) ###########################

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 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.




############ ARTICLE INFO #############

Article Month: October
Article Date: 16/10/2012
Permalink: http://heelpbook.altervista.org/2012/group-by-techniques-sql-server/
Source: http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx
Language: English

View more articles on: http://www.heelpbook.net/
Follow us on Facebook: http://it-it.facebook.com/pages/HeelpBook/100790870008832
Follow us on Twitter: https://twitter.com/#!/HeelpBook
Follow us on RSS Feed: http://feeds.feedburner.com/Heelpbook
Follow us on Delicious: http://delicious.com/heelpbook
Linkedin: http://it.linkedin.com/pub/stefano-maggi/27/73a/b20