How do I use T-SQL Group By

To retrieve the number of widgets from each widget category that has more than 5 widgets, you could do this:

SELECT WidgetCategory, count(*)FROM WidgetsGROUP BY WidgetCategoryHAVING count(*) > 5

The "having" clause is something people often forget about, instead opting to retrieve all their data to the client and iterating through it there.

GROUP BY is similar to DISTINCT in that it groups multiple records into one.

This example, borrowed from http://www.devguru.com/technologies/t-sql/7080.asp, lists distinct products in the Products table.

SELECT Product FROM Products GROUP BY ProductProduct-------------DesktopLaptopMouseNetwork CardHard DriveSoftwareBookAccessory

The advantage of GROUP BY over DISTINCT, is that it can give you granular control when used with a HAVING clause.

SELECT Product, count(Product) as ProdCntFROM ProductsGROUP BY ProductHAVING count(Product) > 2Product      ProdCnt--------------------Desktop          10Laptop            5Mouse             3Network Card      9Software          6

Group By forces the entire set to be populated before records are returned (since it is an implicit sort).

For that reason (and many others), never use a Group By in a subquery.