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
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
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
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]
[..] 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 ...