Multi-Table Reporting with JOIN and GROUP BY: Aggregating Across Relationships in a Single Query

Introduction

You've been handed a request that goes something like this: "Can you pull me a report showing total revenue by sales region, how many orders each customer placed this quarter, and which product categories are driving the most returns?" Every piece of data lives in a different table. Revenue in orders, regions in territories, return reasons in returns. The business wants a single, coherent answer — not three separate spreadsheets they have to paste together manually.

This is where multi-table aggregation earns its place as one of the most essential skills in analytical SQL. The ability to JOIN across relationships and GROUP BY meaningful dimensions simultaneously — in a single query — separates analysts who can answer ad-hoc questions in minutes from those who spend hours copying data between tools. When you combine JOIN with GROUP BY, you're not just retrieving rows from multiple tables; you're reshaping relational data into summarized insight without ever leaving the database.

By the end of this lesson, you'll be writing queries that span three, four, or more tables and produce clean aggregated results. You'll understand why certain JOIN + GROUP BY combinations produce wrong answers (and how to fix them before they make it into a report), how to handle NULLs in aggregations across outer joins, and how to think about query performance when your aggregations involve millions of rows.

What you'll learn:

• How JOIN and GROUP BY interact mechanically, and why order of operations matters
• How to aggregate across one-to-many and many-to-many relationships without inflating counts or sums
• How to use LEFT JOIN with GROUP BY to surface zero-value rows that INNER JOIN would silently drop
• How to handle NULLs introduced by outer joins inside aggregate functions
• Performance strategies for multi-table aggregations at scale, including pre-aggregation and index-aware join ordering

---

Prerequisites

You should be comfortable writing SELECT queries with WHERE and ORDER BY — if you need a refresher, the lesson on SQL Basics: Master SELECT, FROM, WHERE Clauses and Build Your First Queries has you covered. You should also understand the mechanics of JOIN types (INNER, LEFT, RIGHT, FULL OUTER) at a conceptual level — SQL JOINs Explained with Real-World Examples is the right foundation. Finally, you should have worked with at least basic GROUP BY and aggregate functions. If aggregate functions feel unfamiliar, spend time with Master SQL Aggregate Functions: GROUP BY, HAVING, COUNT, SUM, AVG before continuing.

---

The Mechanics: What Happens When JOIN Meets GROUP BY

Before writing a single real-world query, you need to understand the execution order SQL uses when JOIN and GROUP BY appear together. This isn't academic — it's the reason you'll get wrong answers if you don't internalize it.

SQL processes clauses in this logical order:

1. FROM — identify the base table
2. JOIN — expand the working dataset by combining rows from other tables
3. WHERE — filter rows from the expanded dataset
4. GROUP BY — collapse rows into groups
5. HAVING — filter groups
6. SELECT — compute the output columns, including aggregates
7. ORDER BY — sort the final result

The critical insight: JOIN happens before GROUP BY. This means your aggregate functions (COUNT, SUM, AVG) operate on the already-joined, potentially expanded dataset. If a join multiplies rows — which happens naturally in one-to-many relationships — your aggregates will silently operate on more rows than you intended.

Let's make this concrete with a schema we'll use throughout the lesson:

-- A realistic e-commerce schema
CREATE TABLE customers (
    customer_id    INT PRIMARY KEY,
    customer_name  VARCHAR(200),
    region_id      INT,
    signup_date    DATE
);

CREATE TABLE regions (
    region_id    INT PRIMARY KEY,
    region_name  VARCHAR(100),
    country_code CHAR(2)
);

CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT,
    order_date   DATE,
    status       VARCHAR(50)  -- 'completed', 'cancelled', 'returned'
);

CREATE TABLE order_items (
    item_id      INT PRIMARY KEY,
    order_id     INT,
    product_id   INT,
    quantity     INT,
    unit_price   DECIMAL(10,2)
);

CREATE TABLE products (
    product_id      INT PRIMARY KEY,
    product_name    VARCHAR(200),
    category_id     INT,
    cost_price      DECIMAL(10,2)
);

CREATE TABLE categories (
    category_id   INT PRIMARY KEY,
    category_name VARCHAR(100)
);

This schema has multiple levels of one-to-many relationships: one customer has many orders, one order has many order_items, one product belongs to one category. When you join from customers all the way to order_items, each customer row fans out to every order item they've ever purchased. That fan-out is the source of nearly every wrong answer in multi-table aggregation.

---

Building Your First Multi-Table Aggregation

Let's start with a realistic request: "Show me total revenue and order count by customer, for completed orders only."

Revenue lives in order_items (quantity × unit_price). The order status lives in orders. The customer name lives in customers. Three tables, two joins.

SELECT
    c.customer_id,
    c.customer_name,
    COUNT(DISTINCT o.order_id)                          AS order_count,
    SUM(oi.quantity * oi.unit_price)                    AS total_revenue
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id
JOIN order_items oi
    ON o.order_id = oi.order_id
WHERE o.status = 'completed'
GROUP BY
    c.customer_id,
    c.customer_name
ORDER BY total_revenue DESC;

This query is correct. Now let's understand why each piece is essential.

Why COUNT(DISTINCT o.order_id) instead of COUNT(*)?

After the joins expand, each order will appear once per item in that order. An order with 5 line items becomes 5 rows in the working dataset. COUNT(*) would count rows — giving you 5 instead of 1. COUNT(DISTINCT o.order_id) counts unique order IDs, giving you the correct order count regardless of how many items were in each order.

Why GROUP BY includes customer_name?

Most databases require every non-aggregated column in SELECT to appear in GROUP BY. Even though customer_name is functionally dependent on customer_id (a customer can only have one name), standard SQL doesn't know that — you must list it explicitly. Some databases (MySQL with certain modes, SQLite) are more permissive here, but being explicit protects your portability and correctness.

Now let's look at the intermediate state — the expanded join before GROUP BY collapses it — using a smaller example:

customer_id | customer_name | order_id | status    | item_id | quantity | unit_price
----------- | ------------- | -------- | --------- | ------- | -------- | ----------
1001        | Acme Corp     | 5001     | completed | 9001    | 2        | 49.99
1001        | Acme Corp     | 5001     | completed | 9002    | 1        | 129.99
1001        | Acme Corp     | 5002     | completed | 9003    | 3        | 19.99
1002        | Beta Inc      | 5003     | completed | 9004    | 1        | 299.99

After GROUP BY and aggregation:

customer_id | customer_name | order_count | total_revenue
----------- | ------------- | ----------- | -------------
1001        | Acme Corp     | 2           | 289.96
1002        | Beta Inc      | 1           | 299.99

The three rows for Acme Corp collapsed into one: two distinct orders, revenue summed across all three items.

---

The Fan-Out Problem: When Joins Inflate Your Numbers

The fan-out problem is the single most common source of wrong answers in multi-table aggregation. Let's manufacture an example that breaks a naive query.

Suppose you want to add the customer's region to the previous query. Simple enough — just join regions:

-- This query is CORRECT because regions is many-to-one from customers
SELECT
    r.region_name,
    COUNT(DISTINCT c.customer_id)            AS customer_count,
    COUNT(DISTINCT o.order_id)               AS order_count,
    SUM(oi.quantity * oi.unit_price)         AS total_revenue
FROM regions r
JOIN customers c
    ON r.region_id = c.region_id
JOIN orders o
    ON c.customer_id = o.customer_id
JOIN order_items oi
    ON o.order_id = oi.order_id
WHERE o.status = 'completed'
GROUP BY
    r.region_id,
    r.region_name
ORDER BY total_revenue DESC;

This works fine because regions has a one-to-many relationship downward to customers. Each customer row fans out to many orders and items, but the region dimension doesn't cause additional fan-out.

Now suppose you want to add product category information too. The product category is attached to products, which is attached via order_items. So far so good — that path is already traversed. But what if you wanted to simultaneously join a separate promotions table that tracks which promotions applied to each order, and a promotion can apply to multiple orders?

-- DANGER: This creates a many-to-many join that will inflate SUM values
SELECT
    c.customer_id,
    c.customer_name,
    SUM(oi.quantity * oi.unit_price) AS total_revenue  -- WRONG!
FROM customers c
JOIN orders o          ON c.customer_id = o.customer_id
JOIN order_items oi    ON o.order_id = oi.order_id
JOIN order_promotions op ON o.order_id = op.order_id   -- many promotions per order!
WHERE o.status = 'completed'
GROUP BY c.customer_id, c.customer_name;

If a completed order has 3 promotions applied and 2 line items, the join produces 3 × 2 = 6 rows for that order. Your SUM will count those 2 line items' revenue 3 times over. Your report will significantly overstate revenue, and it'll look perfectly reasonable — no errors, no NULLs, just silently wrong numbers.

The fix is pre-aggregation — aggregate on one side before joining:

-- Pre-aggregate promotions, then join the summary
WITH promotion_counts AS (
    SELECT
        order_id,
        COUNT(promotion_id) AS promotions_applied
    FROM order_promotions
    GROUP BY order_id
)
SELECT
    c.customer_id,
    c.customer_name,
    COUNT(DISTINCT o.order_id)               AS order_count,
    SUM(oi.quantity * oi.unit_price)         AS total_revenue,
    SUM(pc.promotions_applied)               AS total_promotions
FROM customers c
JOIN orders o          ON c.customer_id = o.customer_id
JOIN order_items oi    ON o.order_id = oi.order_id
LEFT JOIN promotion_counts pc ON o.order_id = pc.order_id
WHERE o.status = 'completed'
GROUP BY c.customer_id, c.customer_name;

Now promotion_counts has exactly one row per order, so joining it doesn't expand any existing rows. This pattern — pre-aggregate into a CTE, then join the summary — is one of the most powerful tools in your multi-table aggregation arsenal. For more on building complex query architecture this way, see Advanced Subqueries and CTEs: Mastering Complex SQL Query Architecture.

---

LEFT JOIN with GROUP BY: Capturing Zero-Value Rows

INNER JOIN silently drops rows that don't match. That's often exactly what you want — but in reporting contexts, it creates a dangerous gap: categories, regions, or salespeople with zero activity disappear from your report entirely, making it look like the universe of your data is complete when it isn't.

Consider this request: "Show me total revenue by product category, including categories with no sales this quarter."

-- WRONG: INNER JOIN drops categories with no orders
SELECT
    cat.category_name,
    SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM categories cat
JOIN products p        ON cat.category_id = p.category_id
JOIN order_items oi    ON p.product_id = oi.product_id
JOIN orders o          ON oi.order_id = o.order_id
WHERE o.order_date >= '2024-01-01'
  AND o.order_date <  '2024-04-01'
  AND o.status = 'completed'
GROUP BY cat.category_id, cat.category_name
ORDER BY total_revenue DESC;

If "Seasonal Decorations" had zero completed orders this quarter, it won't appear. The report looks complete but it's missing a row.

-- CORRECT: LEFT JOIN preserves all categories
SELECT
    cat.category_name,
    COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS total_revenue,
    COUNT(DISTINCT o.order_id)                     AS order_count
FROM categories cat
LEFT JOIN products p        ON cat.category_id = p.category_id
LEFT JOIN order_items oi    ON p.product_id = oi.product_id
LEFT JOIN orders o          ON oi.order_id = o.order_id
                           AND o.order_date >= '2024-01-01'
                           AND o.order_date <  '2024-04-01'
                           AND o.status = 'completed'
GROUP BY cat.category_id, cat.category_name
ORDER BY total_revenue DESC;

Two critical things changed here:

1. All joins became LEFT JOIN. Once you use LEFT JOIN to preserve the "outer" table, every subsequent join in that chain must also be LEFT JOIN, or you'll lose your outer rows when they fail to match.

2. The date and status filters moved from WHERE to the JOIN ON clause. This is subtle but crucial. If you write WHERE o.order_date >= '2024-01-01', you're filtering after the join. Rows from categories with no matching orders will have NULL in o.order_date, and NULL >= '2024-01-01' evaluates to NULL (which is falsy), filtering out exactly the zero-revenue categories you were trying to preserve. By moving the condition to the JOIN's ON clause, the filter applies during the join — rows that don't meet the condition simply don't produce a match, leaving the outer category row intact with NULLs.

Handling NULLs in aggregates: When SUM receives all NULLs (because a category had no matching order items), it returns NULL, not 0. COALESCE(SUM(...), 0) converts that NULL to a meaningful zero. COUNT(DISTINCT o.order_id) naturally returns 0 when there are no matching orders, because COUNT ignores NULLs and there are no non-NULL order IDs to count.

For a deeper treatment of NULL behavior in SQL, see NULL Handling in SQL: IS NULL, COALESCE, and NULLIF.

---

Aggregating Across Multiple Dimensions Simultaneously

Now we push further. Real reports don't just aggregate on one dimension — they combine multiple dimensions: region × category, customer segment × quarter, product × salesperson. Let's build a query that answers: "Show me total revenue and order count by region and product category for Q1 2024."

SELECT
    r.region_name,
    cat.category_name,
    COUNT(DISTINCT o.order_id)               AS order_count,
    COUNT(DISTINCT c.customer_id)            AS unique_customers,
    SUM(oi.quantity * oi.unit_price)         AS gross_revenue,
    SUM(oi.quantity * (oi.unit_price - p.cost_price)) AS gross_profit
FROM regions r
JOIN customers c        ON r.region_id = c.region_id
JOIN orders o           ON c.customer_id = o.customer_id
                       AND o.order_date >= '2024-01-01'
                       AND o.order_date <  '2024-04-01'
                       AND o.status = 'completed'
JOIN order_items oi     ON o.order_id = oi.order_id
JOIN products p         ON oi.product_id = p.product_id
JOIN categories cat     ON p.category_id = cat.category_id
GROUP BY
    r.region_id,
    r.region_name,
    cat.category_id,
    cat.category_name
ORDER BY
    r.region_name,
    gross_revenue DESC;

This query joins six tables and produces one row per region/category combination that had completed orders in Q1. Let's annotate the design decisions:

• gross_profit calculation at the item level. We compute unit_price - cost_price per item, multiply by quantity, and let SUM accumulate it. This is correct. If we had computed average price or average cost as intermediate values, we'd be averaging averages, which is statistically wrong.
• Date filters on the JOIN. Since this is an INNER JOIN chain, the date condition could also go in WHERE — the result would be the same. I prefer the JOIN clause for readability (the filter is conceptually "part of how we match orders") but this is style, not correctness.
• GROUP BY includes both IDs and names. region_id and category_id are the true keys; including them ensures GROUP BY is tight and deterministic. Including the names makes SELECT valid without requiring MAX() tricks.

---

Conditional Aggregation Within Multi-Table Queries

Multi-table GROUP BY becomes dramatically more powerful when you layer conditional aggregation (CASE WHEN inside aggregate functions) on top. This lets you produce multiple metrics with different filters in a single pass, avoiding multiple subquery joins.

Request: "For each customer, show total revenue, revenue from Electronics specifically, and revenue from orders placed in the last 90 days."

SELECT
    c.customer_id,
    c.customer_name,
    -- Total revenue, all time
    SUM(oi.quantity * oi.unit_price)                                    AS total_revenue,
    -- Revenue from Electronics category only
    SUM(
        CASE WHEN cat.category_name = 'Electronics'
             THEN oi.quantity * oi.unit_price
             ELSE 0
        END
    )                                                                   AS electronics_revenue,
    -- Revenue from last 90 days
    SUM(
        CASE WHEN o.order_date >= CURRENT_DATE - INTERVAL '90 days'
             THEN oi.quantity * oi.unit_price
             ELSE 0
        END
    )                                                                   AS recent_revenue,
    -- Electronics revenue as percent of total
    ROUND(
        100.0 * SUM(
            CASE WHEN cat.category_name = 'Electronics'
                 THEN oi.quantity * oi.unit_price
                 ELSE 0
            END
        ) / NULLIF(SUM(oi.quantity * oi.unit_price), 0),
        2
    )                                                                   AS electronics_pct
FROM customers c
JOIN orders o        ON c.customer_id = o.customer_id
                    AND o.status = 'completed'
JOIN order_items oi  ON o.order_id = oi.order_id
JOIN products p      ON oi.product_id = p.product_id
JOIN categories cat  ON p.category_id = cat.category_id
GROUP BY c.customer_id, c.customer_name
HAVING SUM(oi.quantity * oi.unit_price) > 0
ORDER BY total_revenue DESC;

Note the NULLIF(SUM(...), 0) in the percentage calculation. Division by zero would crash the query for customers with no revenue. NULLIF returns NULL when the denominator is zero, making the division return NULL safely rather than an error. This is standard defensive practice for any division inside an aggregate.

The HAVING clause filters to customers who actually have revenue — a final guard against empty groups. This is a clean example of writing efficient SQL aggregations with HAVING to filter at the group level rather than the row level.

---

Grouping Sets: Multiple Aggregation Levels in One Query

Sometimes you need subtotals at multiple levels simultaneously — totals by region, subtotals by region and category, and a grand total — all in one result set. Writing three separate queries and UNION-ing them works but is expensive and verbose. SQL's GROUPING SETS (and the shorthand ROLLUP and CUBE) solve this elegantly.

SELECT
    r.region_name,
    cat.category_name,
    SUM(oi.quantity * oi.unit_price) AS total_revenue,
    COUNT(DISTINCT o.order_id)       AS order_count
FROM regions r
JOIN customers c     ON r.region_id = c.region_id
JOIN orders o        ON c.customer_id = o.customer_id
                    AND o.status = 'completed'
JOIN order_items oi  ON o.order_id = oi.order_id
JOIN products p      ON oi.product_id = p.product_id
JOIN categories cat  ON p.category_id = cat.category_id
GROUP BY ROLLUP(r.region_name, cat.category_name)
ORDER BY
    GROUPING(r.region_name),
    r.region_name,
    GROUPING(cat.category_name),
    cat.category_name;

ROLLUP(r.region_name, cat.category_name) produces three levels of grouping:

1. Region + Category (detailed rows)
2. Region only (subtotals — category_name is NULL)
3. Grand total (both are NULL)

The GROUPING() function returns 1 when the NULL in that column came from the rollup (not from actual NULL data in the table), 0 otherwise. We use it in ORDER BY to sort subtotals and grand totals below their detail rows rather than intermixing them with genuine NULLs. For a reporting query that goes directly into a dashboard or spreadsheet, ROLLUP can replace an entire pivot table in a single SQL statement.

---

HAVING vs. WHERE in Multi-Table Aggregations

WHERE filters rows before grouping; HAVING filters groups after aggregation. The distinction is basic, but the interaction with multi-table JOINs adds nuance.

Consider: "Show regions where total revenue exceeded $100,000, but only counting completed orders placed by customers who signed up after 2022."

SELECT
    r.region_name,
    SUM(oi.quantity * oi.unit_price) AS total_revenue,
    COUNT(DISTINCT c.customer_id)    AS customer_count
FROM regions r
JOIN customers c    ON r.region_id = c.region_id
                   AND c.signup_date > '2022-12-31'       -- row-level filter
JOIN orders o       ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.status = 'completed'                               -- row-level filter
GROUP BY r.region_id, r.region_name
HAVING SUM(oi.quantity * oi.unit_price) > 100000          -- group-level filter
ORDER BY total_revenue DESC;

The signup_date condition is a property of customers — it's a row-level filter and lives in the JOIN clause (or equivalently in WHERE). The status = 'completed' filter on orders is also row-level. The > 100000 threshold is a property of the group — you can't know total revenue until all rows in a group are summed, so it must live in HAVING.

A common mistake is applying group-level filters in WHERE using a subquery when HAVING would be cleaner and more efficient:

-- Verbose and usually slower
SELECT * FROM (
    SELECT r.region_name, SUM(oi.quantity * oi.unit_price) AS total_revenue
    FROM ...
    GROUP BY r.region_id, r.region_name
) summary
WHERE total_revenue > 100000;

-- Direct and clean
SELECT r.region_name, SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM ...
GROUP BY r.region_id, r.region_name
HAVING SUM(oi.quantity * oi.unit_price) > 100000;

The subquery wrapper adds parsing overhead and obscures intent. Use HAVING for group-level conditions.

---

Performance Considerations for Multi-Table Aggregations

When your tables have millions of rows and you're joining five of them before aggregating, performance stops being an afterthought. Here's a structured approach to thinking about it.

Join Order and Filter Pushdown

Most query optimizers reorder joins automatically, but they need statistics to do so intelligently. In PostgreSQL, SQL Server, and Oracle, running ANALYZE (or UPDATE STATISTICS) regularly ensures the optimizer has current row count and selectivity estimates. Without good statistics, the optimizer may choose a join order that builds a massive intermediate result before filtering it down — reversing the ideal "filter early, join small."

When the optimizer gets it wrong, you can sometimes help with explicit filters that push selectivity early in the plan:

-- Less efficient: joins everything, then filters
SELECT ...
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01'
  AND o.status = 'completed';

-- More efficient hint: filter orders early using a subquery or CTE
WITH recent_completed_orders AS (
    SELECT order_id, customer_id
    FROM orders
    WHERE order_date >= '2024-01-01'
      AND status = 'completed'
)
SELECT ...
FROM customers c
JOIN recent_completed_orders o ON c.customer_id = o.customer_id;

The CTE materializes the filtered, smaller set of orders before joining to customers. On large tables, this can reduce the join's working set from millions to thousands of rows. See SQL Query Optimization: Reading Execution Plans - Advanced Performance Analysis for how to verify this using execution plans.

Indexing for Multi-Table Aggregations

For join columns (customer_id, order_id, product_id), indexes are almost always beneficial. The primary key is indexed by default; foreign key columns often are not, and this is where performance falls apart.

-- Indexes that directly support our query patterns
CREATE INDEX idx_orders_customer_date_status
    ON orders(customer_id, order_date, status);

CREATE INDEX idx_order_items_order_product
    ON order_items(order_id, product_id);

CREATE INDEX idx_customers_region
    ON customers(region_id);

The first index is a composite index on orders that supports filtering by customer_id AND order_date AND status simultaneously. The column order matters: put the equality filter column first (customer_id), then the range filter (order_date), then any remaining filters. For more depth on how indexes physically interact with GROUP BY and aggregation queries, the lesson on SQL Indexes Explained: How They Work and When to Create Them goes deep on this.

Pre-Aggregation Patterns

For reporting queries that run repeatedly on large datasets, pre-aggregating into a summary table or materialized view is often the right architectural choice:

-- A summary table that can be updated incrementally
CREATE TABLE daily_revenue_summary (
    summary_date   DATE,
    region_id      INT,
    category_id    INT,
    order_count    INT,
    unique_customers INT,
    gross_revenue  DECIMAL(15,2),
    PRIMARY KEY (summary_date, region_id, category_id)
);

-- Populate or refresh with your multi-table query
INSERT INTO daily_revenue_summary
SELECT
    o.order_date,
    c.region_id,
    p.category_id,
    COUNT(DISTINCT o.order_id),
    COUNT(DISTINCT c.customer_id),
    SUM(oi.quantity * oi.unit_price)
FROM orders o
JOIN customers c    ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p     ON oi.product_id = p.product_id
WHERE o.status = 'completed'
  AND o.order_date = CURRENT_DATE - 1   -- yesterday's data
GROUP BY o.order_date, c.region_id, p.category_id
ON CONFLICT (summary_date, region_id, category_id)
DO UPDATE SET
    order_count      = EXCLUDED.order_count,
    unique_customers = EXCLUDED.unique_customers,
    gross_revenue    = EXCLUDED.gross_revenue;

Dashboard queries can then hit the summary table instead of joining six tables on every render. This pattern is what powers most production BI systems at scale.

---

Subquery and CTE Decomposition for Clarity and Correctness

Complex multi-table aggregations become increasingly hard to reason about as a monolithic query. Breaking them into CTEs (Common Table Expressions) serves two purposes: it makes the query human-readable, and it gives you natural checkpoints to verify intermediate results.

Here's a production-style query broken into CTEs for a quarterly business review report:

WITH
-- Step 1: Filter to completed orders in the quarter
quarterly_orders AS (
    SELECT
        order_id,
        customer_id,
        order_date
    FROM orders
    WHERE status = 'completed'
      AND order_date >= '2024-01-01'
      AND order_date <  '2024-04-01'
),

-- Step 2: Compute item-level revenue with product metadata
item_revenue AS (
    SELECT
        oi.order_id,
        p.category_id,
        oi.quantity * oi.unit_price               AS line_revenue,
        oi.quantity * (oi.unit_price - p.cost_price) AS line_profit
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
),

-- Step 3: Aggregate to order level
order_summary AS (
    SELECT
        qo.order_id,
        qo.customer_id,
        SUM(ir.line_revenue) AS order_revenue,
        SUM(ir.line_profit)  AS order_profit
    FROM quarterly_orders qo
    JOIN item_revenue ir ON qo.order_id = ir.order_id
    GROUP BY qo.order_id, qo.customer_id
),

-- Step 4: Aggregate to customer level with regional data
customer_summary AS (
    SELECT
        c.customer_id,
        c.customer_name,
        r.region_name,
        COUNT(os.order_id)       AS order_count,
        SUM(os.order_revenue)    AS total_revenue,
        SUM(os.order_profit)     AS total_profit
    FROM order_summary os
    JOIN customers c ON os.customer_id = c.customer_id
    JOIN regions r   ON c.region_id = r.region_id
    GROUP BY c.customer_id, c.customer_name, r.region_name
)

-- Final output: top customers by revenue with profit margin
SELECT
    customer_name,
    region_name,
    order_count,
    total_revenue,
    total_profit,
    ROUND(100.0 * total_profit / NULLIF(total_revenue, 0), 2) AS profit_margin_pct
FROM customer_summary
WHERE total_revenue >= 1000    -- filter small accounts from the report
ORDER BY total_revenue DESC
LIMIT 50;

Each CTE has a single responsibility and can be tested in isolation by selecting from it directly. quarterly_orders can be verified with a simple SELECT COUNT(*). item_revenue can be inspected for a known order. order_summary can be cross-checked against a known customer's order history. This decomposition doesn't just help readability — it's an active debugging strategy.

---

Hands-On Exercise

Work through this multi-part exercise using the schema defined at the beginning of this lesson. Populate it with realistic test data if you have a database available.

The scenario: You're a data analyst at an e-commerce company preparing a quarterly performance report. The VP of Sales has asked for three specific metrics.

Part 1: Regional Sales Summary

Write a query that returns, for Q1 2024 (completed orders only):

• region_name
• total_orders — distinct count of orders
• total_customers — distinct count of customers who placed at least one order
• gross_revenue — sum of quantity × unit_price across all items
• avg_order_value — gross_revenue divided by total_orders

Include all regions, even those with zero orders. Regions with no orders should show 0 for revenue (not NULL).

Expected challenges: You'll need LEFT JOIN with date filters in ON, COALESCE for zero-revenue rows, and NULLIF for the avg_order_value division.

Part 2: Category Performance Breakdown

Write a query showing, for each category:

• category_name
• unique_products_sold — count of distinct products that appear in at least one completed order item
• total_units_sold — sum of quantity
• gross_revenue
• avg_unit_price — average unit_price weighted by quantity (total revenue ÷ total units)

Restrict to completed orders in 2024 (any quarter). Sort by gross_revenue descending.

Expected challenges: Distinguishing between product count in the products table (all products) versus products actually sold. The weighted average requires dividing SUM(quantity × unit_price) by SUM(quantity), not AVG(unit_price).

Part 3: Customer Segmentation Report

Write a query that classifies customers into revenue tiers for 2024 completed orders:

• VIP: total_revenue ≥ $10,000
• Active: total_revenue between $1,000 and $9,999
• Occasional: total_revenue between $1 and $999
• Inactive: no completed orders in 2024

For each tier, return:

• tier (the label above)
• customer_count
• total_revenue_in_tier
• avg_revenue_per_customer

Expected challenges: You'll need LEFT JOIN to capture Inactive customers, a CASE expression to build the tier label, and then GROUP BY on the derived tier. One approach is a CTE that computes per-customer revenue first, then a wrapper query that applies the tier logic.

---

Common Mistakes & Troubleshooting

Mistake 1: SUM or COUNT inflated by unexpected fan-out

Symptom: Revenue numbers are 2× or 5× higher than you expect. Order counts are wrong. Diagnosis: Add COUNT(*) to your query alongside your aggregate. Compare it to COUNT(DISTINCT order_id). If they're wildly different, you have fan-out. Fix: Identify which join is creating the fan-out. Either move to COUNT(DISTINCT) for counting, or pre-aggregate the fan-out side into a CTE before joining.

Mistake 2: LEFT JOIN silently converted to INNER JOIN

Symptom: You used LEFT JOIN but categories/regions with no data still disappear. Diagnosis: Check your WHERE clause for conditions on the right-side table (WHERE o.status = 'completed' when orders is on the right of a LEFT JOIN). Fix: Move conditions on the nullable right-side table from WHERE to the JOIN's ON clause.

Mistake 3: Aggregating a derived expression incorrectly

Symptom: Average order value is wrong, or profit margin is off. Common error:

-- WRONG: this averages the averages, not the weighted average
AVG(oi.unit_price) AS avg_price

Fix: For weighted metrics, always compute via SUM ÷ SUM:

-- CORRECT: weighted average unit price
SUM(oi.quantity * oi.unit_price) / NULLIF(SUM(oi.quantity), 0) AS weighted_avg_price

Mistake 4: GROUP BY on a non-deterministic column

Symptom: In strict SQL mode, you get an error: "Column 'category_name' must appear in the GROUP BY clause." In loose mode (MySQL default), you get a query that runs but returns an arbitrary value for that column. Fix: Include every non-aggregated column from SELECT in your GROUP BY. Include both the ID column and the name column explicitly.

Mistake 5: HAVING used where WHERE would be more efficient

Symptom: Query is slow even though the result set is small. Diagnosis: You have conditions in HAVING that could be in WHERE (e.g., HAVING category_name = 'Electronics'). Fix: Row-level conditions belong in WHERE so they filter before aggregation. Only use HAVING for conditions that reference aggregate results (SUM, COUNT, etc.).

Mistake 6: Joining without a real relationship

Symptom: Result set is astronomically large — a Cartesian product. Example: Forgetting the ON clause, or joining on a column that's not actually the key. Diagnosis: Check row count immediately. If it's the product of the two table sizes, you have a cross join. Fix: Verify every JOIN has a meaningful ON condition connecting primary key to foreign key.

---

Summary & Next Steps

Multi-table aggregation is where SQL transitions from data retrieval to analytical power. The core pattern — JOIN to assemble related data, GROUP BY to summarize it — is deceptively simple on the surface but harbors genuine complexity in how join cardinality interacts with aggregation, how LEFT JOIN preserves outer rows that INNER JOIN drops, and how to decompose complex logic into readable CTEs that are individually verifiable.

The key principles to carry forward:

1. Understand cardinality before you aggregate. One-to-many joins fan out rows. Know which table is on which side of every relationship before writing aggregates.
2. Use COUNT(DISTINCT key) when counting entities, not COUNT(*) which counts rows in the joined result.
3. Move filter conditions on right-side tables from WHERE to ON when using LEFT JOIN.
4. Pre-aggregate before joining when you have multiple independent one-to-many paths that would create fan-out inflation.
5. Break complex queries into CTEs for debuggability and readability.
6. Think about performance from the start — index foreign key columns, push filters early, and consider pre-aggregation for repeated reporting queries.

Where to go next: Once you're comfortable with multi-table aggregation, the natural progression is adding window functions to your analytical toolkit — they let you compute running totals, rank customers within regions, and compare current period to previous period without collapsing rows the way GROUP BY does. See Window Functions: RANK, ROW_NUMBER, and LAG to continue building. For more advanced query decomposition, Advanced Subqueries and CTEs: Mastering Complex SQL Query Architecture covers recursive patterns, lateral joins, and optimization strategies that extend directly from what you've learned here.