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 11.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 as f(a).

Figure 11.56: Partitioning rows into groups with the same value for column 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 group, each select-item has only one value. To ensure this, each group property must be either a grouping criterion (a column name used for grouping), a function call (e.g., count (*)), a constant, or an expression formed from these terms (e.g., 2 * groupcol). The basic syntax is as follows:

<b class="bold">select</b> <i class="emphasis">group-property1,</i> ...<b class="bold">from</b> ...[<b class="bold">where</b> ...]<b class="bold">group by</b> <i class="emphasis">group-criterion1</i>, ...

Table 11.17 will be used to help explain most of these ideas. The relation scheme is Particle ( particleName , family, charge, mass ).