Using group by the right way

Here are two great articles about the usage of Group By:

SQL GROUP BY techniques
More on GROUP BY; Examining SUM(Distinct)

Thanks to Jeff Smith.

In a nut-shell, instead of this:

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

Use this:

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

This way you don’t need to include bunch of fields in your group by clause unnecessarily.