Group By

Contents

5.12. Group By#

It can often be useful to group data by a particular attribute. For example, consider the following table.

../../_images/groupby_table-be955ca732b79ddc.png

Let’s suppose we want to see how students performed on the exam BY class. In this case, there are two classes, class A and class B. What we can do, is GROUP BY class. The structure of a GROUP BY statement:

GROUP BY attribute;

The GROUP BY clause goes after a WHERE clause.

We then need to provide a function that summarises information about each group. The functions we can use are:

  • COUNT()

  • MAX()

  • MIN()

  • SUM()

  • AVG()

This whole process can be summarised by the figure below.

../../_images/groupby_exploded-9c7b7c11b095c9b1.png

These functions go in the SELECT statement. Here is an example.

SELECT AVG(exam_mark), class
FROM marks
GROUP BY class

5.12.1. Glossary#

Group#

Collect rows that share the same value for an attribute so summary functions can be applied to each collection.

GROUP BY#

SQL clause used to group rows by a particular attribute.

Contents