JOINs with Aggregation
What are JOINs with Aggregation?
Combining JOIN with aggregate functions like COUNT, SUM, and AVG lets you summarize related data across multiple tables. This is one of the most practical SQL patterns.
Syntax
SELECT t1.column, AGGREGATE(t2.column)
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.fk_id
GROUP BY t1.column;When to Use
- Counting orders per customer
- Calculating total revenue per product
- Finding the average grade per course
- Reporting on any grouped, cross-table metric
Key Points
- GROUP BY Required — When mixing non-aggregated columns with aggregate functions, every non-aggregated column must appear in
GROUP BY. - LEFT JOIN for Zeros — Use LEFT JOIN if you want to include entities with zero related records. Use
COALESCE(COUNT(...), 0). - HAVING for Filters — Filter aggregated results with
HAVING, notWHERE. - Count Carefully —
COUNT(*)counts all joined rows.COUNT(t2.id)counts only non-NULL matches. - Performance — Aggregate after joining. If possible, filter with WHERE before aggregation to reduce the data set.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
JOINs with Aggregation Challenge
Write a query that solve this task: show each student and how many courses they are enrolled in.
Expected result
Each student name with the count of their enrollments, including 0.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What are JOINs with Aggregation?
Press Run to execute the query once the engine is ready.
More Examples
Total revenue per product
Calculate total order revenue for each product.
Press Run to execute the query once the engine is ready.
Average salary per department
Find the average salary in each department.
Press Run to execute the query once the engine is ready.