Using Joins with Aggregations
You'll learn how to combine JOIN operations with GROUP BY and aggregate functions to calculate summary statistics across related tables.
The Core Idea
When you work with relational databases, you often need to answer questions that span multiple tables and require calculations. For example: "How many orders has each customer placed?" or "What's the total revenue per product category?"
This requires two skills working together:
- JOINs connect related tables
- Aggregations (COUNT, SUM, AVG, etc.) summarize data
By combining them, you first join tables to bring related data together, then group and aggregate to produce meaningful summaries.
How It Works
The pattern is straightforward:
- Use a JOIN to connect tables based on their relationships
- Apply GROUP BY to organize rows into groups
- Use aggregate functions (COUNT, SUM, AVG, MAX, MIN) to calculate values for each group
SELECT customers.name, COUNT(orders.id) AS total_orders
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.id, customers.name;
This query joins customers with their orders, then counts how many orders each customer has placed—even customers with zero orders (thanks to LEFT JOIN).
Common Patterns
- Counting related records: How many items per category?
- Summing values: Total sales per region?
- Averaging metrics: Average order value per customer?
- Finding extremes: Maximum price in each department?
Remember that your SELECT clause can only include columns from GROUP BY or aggregate functions—anything else will cause an error.
Key Takeaway: Joins with aggregations let you answer cross-table questions requiring calculations by first connecting related data, then grouping and summarizing it with aggregate functions.