Summarising Data: COUNT, SUM, GROUP BY

Introduction

So far each result row came from one table row. Often you want a summary instead: how many products, the average price, the total stock. Functions that squash many rows into one value are called aggregate functions.

The five aggregate functions

• COUNT(*) counts rows.
• SUM adds values, AVG averages them.
• MIN and MAX find the smallest and largest.

They respect WHERE, which filters rows before they are summarised:

COUNT(column) counts only rows where that column is not NULL, while COUNT(*) counts all rows. Compare them:

GROUP BY: one answer per group

GROUP BY splits rows into groups and gives one result row per group. This answers "for each category, ...".

A rule to remember: every column in SELECT must either be in the GROUP BY or be wrapped in an aggregate function.

HAVING: filter the groups

WHERE filters rows before grouping. HAVING filters groups after the summary is calculated. Use it when the condition involves an aggregate.

The full clause order is:

1. SELECT
2. FROM
3. WHERE (rows)
4. GROUP BY
5. HAVING (groups)
6. ORDER BY
7. LIMIT

Put it together

Which categories have a total stock above 150 units, with the biggest first?

Try it yourself

For each customer city, count how many customers live there. Leave out customers whose city is missing, and show the largest cities first.

Summary

• COUNT, SUM, AVG, MIN and MAX turn many rows into one value.
• COUNT(*) counts all rows. COUNT(column) skips NULLs.
• GROUP BY gives one result row per group.
• WHERE filters rows before grouping, and HAVING filters groups after.