GROUP BY

As has been explained in the previous part, aggregate functions can be used to deduct information about the table as a whole. When these functions are used, this will result in a single cell which contains the requested number. We can take this principe further by working with groups. When the GROUP BY clause is used, the table will be first be divided into groups. Next, an aggregate function will be applied to each different group. Each group will then result in a single row in the table. GROUP BY can only be used if you also use an aggregate function in the SELECT clause.

The keyword GROUP BY should be followed by the name of a column. All the records that have the same value in the column that you mention will become part of the same group. The GROUP BY clause is useful only if such a division into groups is possible. GROUP BY [PRIMARY KEY] is not useful because this will not result in different groups, as each record should have a unique primary key.

It is important to remember that, if you use GROUP BY, you are not free to display all the columns that you want. After SELECT, you can only mention:

1. an aggregate function
2. the columns that also appear after GROUP BY

To get an overview of the number of treasures for each library, the following query can be used:

Exercises

1. For each library, select the oldest treasure
2. For each country in the table CREATOR, display the number of persons