Grouping on One Column
The simplest form of the GROUP BY clause is the one in which only one column is grouped. In the previous chapters, we gave several examples of statements with such a GROUP BY clause. For the sake of clarity, we specify several other examples in this section.
Figure. Get all the different town names from the PLAYERS table.
Explanation: All rows with the same TOWN form one group. Each row in the intermediate result has one value in the TOWN column, whereas all other columns can contain multiple values. To indicate that these columns are special, the values are placed between brackets. We show those columns in this way for illustrative purposes only; you should realize that SQL probably would solve this internally in a different way. Furthermore, these two columns cannot be presented like this. In fact, a column that is not grouped is completely omitted from the end result, but we return to this later in the chapter.
The end result of the statement is:
TOWN --------- Stratford Midhurst Inglewood Plymouth Douglas Eltham
A frequently used term in this particular context is grouping. The GROUP BY clause in the previous statement has one grouping, which consists of only one column: the TOWN column. In this chapter, we sometimes represent this as follows: The result is grouped by [TOWN]. Later in this chapter, we give examples of groupings with multiple columns and GROUP BY clauses consisting of multiple groupings.
We could have solved the earlier question more easily by leaving out the GROUP BY clause and adding DISTINCT to the SELECT clause (work this out for yourself). Using the GROUP BY clause becomes interesting when we extend the SELECT clause with aggregation functions.
2. For each town, find the number of players.
Explanation: In this statement, the result is grouped by [TOWN]. The COUNT(*) function is now executed against each grouped row instead of against all rows. In other words, the function COUNT(*) is calculated for each grouped row (for each town).
In this result, it is obvious that the data is aggregated. The individual data of players cannot be displayed anymore, and the data is aggregated by TOWN. Or the aggregation level of this result is TOWN.
3. For each team, get the team number, the number of matches that has been played for that team, and the total number of sets won.
Explanation: This statement contains one grouping consisting of the TEAMNO column.
4. For each team that is captained by a player resident in Eltham, get the team number and the number of matches that has been played for that team.
The column on which the result has been grouped might also appear in the SELECT clause as a parameter within an aggregation function. This does not happen often, but it is allowed.
Figure. Get each different penalty amount, followed by the number of times that the amount occurs, in the PENALTIES table, and also show the result of that amount multiplied by the number.