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

1. GROUP BY Required — When mixing non-aggregated columns with aggregate functions, every non-aggregated column must appear in GROUP BY.
2. LEFT JOIN for Zeros — Use LEFT JOIN if you want to include entities with zero related records. Use COALESCE(COUNT(...), 0).
3. HAVING for Filters — Filter aggregated results with HAVING, not WHERE.
4. Count Carefully — COUNT(*) counts all joined rows. COUNT(t2.id) counts only non-NULL matches.
5. Performance — Aggregate after joining. If possible, filter with WHERE before aggregation to reduce the data set.