MEPX
Chapter 5 of 10All chapters
  1. 1What SQL is
  2. 2SELECT and WHERE
  3. 3NULL
  4. 4Sorting and limiting
  5. 5Aggregates and GROUP BY
  6. 6Joins
  7. 7Keys and relationships
  8. 8Subqueries and CTEs
  9. 9Indexes and performance
  10. 10Changing data

Chapter 5 of 10

Aggregates and GROUP BY

Collapsing many rows into one.

The functions

COUNT, SUM, AVG, MIN and MAX reduce many rows to a single value. GROUP BY splits the rows into groups first, so you get one result row per group.

  • COUNT(*) counts rows; COUNT(column) counts non-NULL values in that column.
  • Every selected column must be grouped or aggregated.

WHERE against HAVING

WHERE filters rows before grouping and HAVING filters groups afterwards. That is why HAVING can test an aggregate such as COUNT(*) > 5 and WHERE cannot.