Lesson 13 of 15 · 20 min
Aggregating after joins
By the end of this lesson you can write a joined aggregate report that groups by a key (not just a label), keeps zero rows with LEFT JOIN and COALESCE, and decide when to aggregate before joining instead of after.
A joined report has two kinds of columns:
- Measures: numbers you add up or count, such as line value, quantity, freight cost. They live in a detailed (fact) table.
- Attributes: labels you break the numbers down by, such as category name, carrier, customer. They live in the tables the fact table points to.
The standard shape is: start from the table where the measure lives, join outward to the attribute tables (many-to-one joins, which never multiply rows), filter, then GROUP BY the attributes. Because every join goes from the detailed table towards a parent, each measure is still counted exactly once.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons