ImplementationGROUP BY

GROUP BY is another SQL keyword that we can use to group a set of records in our query by some given criteria. It gathers any data that is identical in the field we want to GROUP BY.

This criteria can change based on what field we want to GROUP BY.

In the following example, we will group pupils by their class and order them by their class in ascending order.

SELECT pupilID, firstname, surname, class, pastoralCareTeacher, meritPoints

FROM Pupil

GROUP BY class

ORDER BY class ASC;

Note: whenever a single query has both GROUP BY and ORDER BY clauses, the GROUP BY clause must precede the ORDER BY clause. If you do not do this, you will get an error.

If we run the query above, a table of results will be returned

Our table would look this this:

The table above has all pupils grouped by their class and ordered by their class in ascending order (1I1, 1I2, 1M1, 1M2, 1T1, 1T2).