SQL: Visual QuickStart Guide

Grouping Rows with GROUP BY

To this point, I’ve used aggregate functions to summarize all the values in a column or just those values that matched a WHERE search condition. You can use the GROUP BY clause to divide a table into logical groups (categories) and calculate aggregate statistics for each group.

An example will clarify the concept. Listing 6.9 uses GROUP BY to count the number of books that each author wrote (or co-wrote). In the SELECT clause, the column au_id identifies each author, and the derived column num_books counts each author’s books. The GROUP BY clause causes num_books to be calculated for every unique au_id instead of only once for the entire table. Figure 6.9 shows the result. In this example, au_id is called the grouping ...