GROUP BY function in SQL

Hey Guys,
I learn couple of important things about group by function that:
GROUP BY doesn’t allow aggregate functions
As with any other group, the SELECT and GROUP BY lists must match. Including an aggregate in the SELECT clause is the only exception to this rule.

SELECT ZIP FROM Customers GROUP BY ZIP

Include only those columns that define the group in both the GROUP BY and SELECT column lists. In other words, the SELECT list must match the GROUP BY list, with one exception: The SELECT list can include aggregate functions. (GROUP BY doesn’t allow aggregate functions.)
In addition, you can’t refer to an aliased field in the GROUP BY clause

GROUP BY ALL

SELECT ZIP, Count(ZIP) AS KYCustomersByZIP FROM Customers WHERE State = ‘KY’ GROUP BY ALL ZIP

The resulting groups comprise all ZIP values in the underlying data. However, the aggregate column (KYCustomersByZIP) would display 0 for any group other than a Kentucky ZIP.

Remote queries don’t support GROUP BY ALL.

Usually, you’ll use HAVING to evaluate a group using an aggregate. For instance, the following statement returns a unique list of ZIP codes, but the list might not include every ZIP code in the underlying data source:
SELECT ZIP, Count(ZIP) AS CustomersByZIP FROM Customers GROUP BY ZIP HAVING Count(ZIP) = 1

Only those groups with just one customer make it to the results.

Get a closer look at WHERE and HAVING

If you’re still confused about when to use WHERE and when to use HAVING, apply the following guidelines:

* WHERE comes before GROUP BY; SQL evaluates the WHERE clause before it groups records.
* HAVING comes after GROUP BY; SQL evaluates HAVING after it groups records.

Summarise the aggregate

You can further summarise data by displaying a subtotal for each group. SQL’s ROLLUP operator displays an extra record, a subtotal, for each group. That record is the result of evaluating all the records within each group using an aggregate function. The following statement totals the OrderTotal column for each group:
SELECT Customer, OrderNumber, Sum(Cost * Quantity) AS OrderTotal FROM Orders GROUP BY Customer, OrderNumber WITH ROLLUP

The ROLLUP row for a group with two OrderTotal values of 20 and 25 would display an OrderTotal of 45. The first record in a ROLLUP result is unique because it evaluates all of the group records. That value is a grand total for the entire recordset.

ROLLUP doesn’t support DISTINCT in aggregate functions or the GROUP BY ALL clause.

The CUBE operator goes a step further than ROLLUP by returning totals for each value in each group. The results are similar to ROLLUP, but CUBE includes an additional record for each column in the group. The following statement displays a subtotal for each group and an additional total for each customer:
SELECT Customer, OrderNumber, Sum(Cost * Quantity) AS OrderTotal FROM Orders GROUP BY Customer, OrderNumber WITH CUBE

CUBE doesn’t support GROUP BY ALL.

Bring order to summaries

When the results of a CUBE are confusing (and they usually are), add the GROUPING function as follows:
SELECT GROUPING(Customer), OrderNumber, Sum(Cost * Quantity) AS OrderTotal FROM Orders GROUP BY Customer, OrderNumber WITH CUBE

The results include two additional values for each row:

* The value 1 indicates that the value to the left is a summary value–the result of the ROLLUP or CUBE operator.
* The value 0 indicates that the value to the left is a detail record produced by the original GROUP BY clause.

Ref: http://www.builderau.com.au/program/sqlserver/soa/10-tips-for-sorting-grouping-and-summarizing-SQL-data/0,339028455,339274648,00.htm