SQL GROUP BY techniques

One aspect of 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

If you observe in detail, SELECT's written this way 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

Now this 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.
Courtesy: http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx