Group By

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.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.png

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

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