Module
Module 3 of 6Lesson 2 of 3~14 min

Count, sum, group

How many active accounts, what revenue per plan, what share of a given status: these monthly-review questions all rely on aggregation. The lesson shows how to summarize a table and compare groups in SQL, and how to avoid the most common counting mistakes. You then present numbers the data team can sign off on without redoing them.

Take this lesson: your first course is freeSee the full syllabus

Lesson objective

By the end of this lesson, you will be able to summarise a table with aggregate functions (COUNT, SUM, AVG, MIN, MAX), compute these summaries per group with GROUP BY, filter groups with HAVING and count a subset of rows with CASE WHEN or COUNT(DISTINCT).

Topics covered

  • GROUP BY
  • COUNT DISTINCT
  • HAVING
  • aggregate functions
  • product metrics

Where it fits

First queries

How do I get a reliable number from a table myself, without being caught out by missing values and dates?

Lessons in this module

  1. Load the dataset and write your first SELECT queries
  2. Count, sum, group (this lesson)
  3. Missing values and dates, two traps that produce wrong numbers

What you will learn in the course

This lesson is part of the course Model your product’s data and query it with SQL

  • Read a data model (tables, primary and foreign keys, cardinalities, entity-relationship diagram) and connect it to the product's screens and rules.
  • Model the data of a feature (entities, attributes, relationships, keys, cardinalities) and get the model validated by the engineering team.
  • Compare a relational database and a document (NoSQL) database for a product need and justify the choice based on expected queries, consistency and change.
  • Write queries that filter, sort and aggregate (SELECT, WHERE, ORDER BY, GROUP BY, HAVING) while handling NULL values and dates correctly.
  • Combine several tables with INNER and LEFT joins, and check that the result neither loses nor duplicates any row.
  • Turn a product question into a verifiable query, using CTEs and simple window functions (ROW_NUMBER, LAG, SUM OVER).
  • Query data read-only while protecting personal data, and have an AI draft SQL while checking every query before using its result.

Related courses