Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

III.6.2. Grouping Results with GROUP BY

The GROUP BY clause can be used in a SELECT list to provide summary data. GROUP BY is most often used with aggregate functions. Aggregate functions are those that work on a group of data and return a single result. Some of the common aggregate functions you use with GROUP BY clauses are

• SUM: SUM adds all the values in a group and returns the result. Only numbers can be added.

• AVG: AVG provides an average of the values in a group by adding all the non-null values and dividing the sum by the number of values added. NULL values aren't included in the computation. Only numbers can be averaged.

• MIN: MIN (minimum) identifies the lowest value item within the column. NULL values are ignored. MIN works with numbers, text, and dates.

• MAX: MAX (maximum) identifies the highest value item within the column. NULL values are ignored. MAX works with numbers, text, and dates.

• COUNT: COUNT returns the number of rows in a table or result set. COUNT(*) includes the NULL and duplicate values in the result. COUNT(expression) returns the number of items in a group that match the expression (such as a column name) ignoring NULL and duplicate values. Because COUNT is used to count rows, it works on any data type.

Aggregate functions often work on numbers. Within SQL Server 2008, numbers are stored in numeric data types, such as tinyint (tiny integer), smallint (small integer), int (integer), bigint (big integer), decimal, money, smallmoney, float, and real.

The generic ...