Information Modeling and Relational Databases, 2nd Edition

12.10. SQL: Grouping

Sometimes we wish to partition the rows of a table into a number of groups and display properties that apply to each group as a whole. SQL provides a group by clause that may be included in the select statement to retrieve such grouped data. Each group is a bag of rows with the same value(s) for the grouping criteria listed in the group by clause. The final query result has at most one row for each group. Figure 12.56 pictures grouping by column a. Here there are three groups, one for each of the a values. The result may include some group property or function of a, depicted here asf(a).

Since the final result includes just one row for each group, the select list may include group properties only. In other words, for each ...