Learning SQL on SQL Server 2005

Chapter 9. Aggregation and GROUP BY

The SQL construction GROUP BY is a SELECT statement clause that is designed to be used in conjunction with aggregation (discussed in Chapter 5) to group data of similar types. An aggregate function is one that extracts information—such as a COUNT of rows or an average, minimum, or maximum—by operating on multiple rows. We first discuss using GROUP BY on one column, and then on two columns. Then, we look at how to use GROUP BY in conjunction with the ORDER BY, HAVING, and WHERE clauses. Finally, we discuss aggregation with subqueries and complexities that nulls present in aggregate functions and other queries. As we introduce the GROUP BY and HAVING, and expand on the ORDER BY (which has been introduced earlier) in this chapter, we first present a SELECT in modified BNF showing the GROUP BY, HAVING and ORDER BY, before we start the rest of the discussion.

A SELECT in Modified BNF

BNF, short for Backus Naur Form, is used to describe syntax rules. A general form (in modified BNF) of the SELECT statement for SQL Server, with the FROM, WHERE, GROUP BY, HAVING and ORDER BY would be:

SELECT result-set
[FROM Tables]
[WHERE row-filter]
[GROUP BY column names]
[HAVING after-filter on groups]
[ORDER BY column names]

The [..] notation means that the contained code is optional.

The GROUP BY Clause

GROUP BY is used in conjunction with aggregate functions to group data on the basis of the same values in a column. GROUP BY returns one row for each value of the ...