Filtering Aggregated Results Across Multiple Tables: Combining JOIN, GROUP BY, and HAVING in One Query

Introduction

Imagine you're a data analyst at a mid-sized e-commerce company. Your manager walks over and asks: "Which customers have placed more than three orders in the last year, and what's their total spend?" You know the orders are in one table and the customer names are in another. You also know you need to count and sum things — but you need to filter those counts after the grouping happens, not before. You sit down at your keyboard and realize this is the moment where several SQL concepts collide at once.

This is exactly the kind of query that separates people who can read SQL from people who can write SQL. When you need to join multiple tables together, group the combined rows by some category, and then filter on the results of that grouping, you're stacking three of SQL's most powerful clauses in a single query. If you've been writing each of these in isolation, this lesson is where you bring them together.

By the end of this lesson, you'll be able to write confident, multi-table queries that aggregate data and filter on those aggregates — the exact skill you need for real business reporting.

What you'll learn:

• How JOIN, GROUP BY, and HAVING interact within a single query
• The order SQL evaluates these clauses — and why it matters
• How to correctly filter on aggregate values vs. raw column values
• Common mistakes that produce wrong or empty results
• How to read and build these queries incrementally so they don't feel overwhelming

---

Prerequisites

You should be comfortable with the basics before tackling this lesson. Specifically, you'll want to understand how SELECT, FROM, and WHERE clauses work and how JOINs connect rows across tables. You should also have a working understanding of GROUP BY, COUNT, SUM, and AVG — this lesson extends those concepts into multi-table territory.

---

The Scenario: An E-Commerce Database

Throughout this lesson, we'll work with a simple but realistic database structure. Here are the tables we'll use:

customers

customer_id	first_name	last_name	city
1	Priya	Mehta	Chicago
2	Jordan	Ellis	Austin
3	Sam	Nguyen	Chicago
4	Keiko	Tanaka	Portland

orders

order_id	customer_id	order_date	status
101	1	2024-02-10	completed
102	1	2024-05-22	completed
103	1	2024-08-01	completed
104	1	2024-11-14	completed
105	2	2024-03-08	completed
106	2	2024-07-19	refunded
107	3	2024-06-30	completed
108	4	2024-09-05	completed
109	4	2024-12-01	completed

order_items

item_id	order_id	product_name	quantity	unit_price
1	101	Wireless Keyboard	1	45.00
2	102	USB Hub	2	29.99
3	103	Monitor Stand	1	62.00
4	104	Webcam	1	89.00
5	105	Mouse Pad	3	12.00
6	106	Laptop Sleeve	1	38.00
7	107	Desk Lamp	2	24.50
8	108	Phone Stand	1	18.00
9	109	Webcam	1	89.00

---

How the Three Clauses Work Together

Before writing the full query, let's be precise about what each clause does in this context.

JOIN combines rows from multiple tables based on a matching condition. It runs first (conceptually), creating a wider, combined dataset. When you join customers to orders, you get one row per order, with the customer's name attached.

GROUP BY then takes that combined dataset and collapses rows with the same value in the specified column into a single summary row. Each group gets one output row.

HAVING filters those grouped summary rows based on the result of an aggregate function — like keeping only groups where COUNT(*) > 3. This is the part that's genuinely different from WHERE: WHERE filters individual rows before grouping, while HAVING filters summary rows after grouping.

The logical order of execution in SQL is:

1. FROM + JOIN — assemble the data
2. WHERE — filter individual rows
3. GROUP BY — group the remaining rows
4. HAVING — filter the groups
5. SELECT — choose what to show
6. ORDER BY — sort the result

This order explains a rule that trips up nearly every beginner: you cannot use a column alias defined in SELECT inside a WHERE or HAVING clause in most databases, because SELECT runs after both of them. The data isn't named yet when the filter is applied.

---

Building the Query Piece by Piece

The best way to write a complex query is to build it incrementally. Don't try to write the whole thing at once. Start with the JOIN, verify it looks right, then add GROUP BY, then add HAVING.

Step 1: The JOIN

Let's start by connecting customers to orders. The link is customer_id.

SELECT
    customers.customer_id,
    customers.first_name,
    customers.last_name,
    orders.order_id,
    orders.order_date
FROM customers
INNER JOIN orders
    ON customers.customer_id = orders.customer_id;

This gives you one row for every order, with the customer's name attached. Priya Mehta appears four times because she has four orders. Run this and inspect the output. If you see something unexpected here — extra rows, missing customers — fix it before moving on.

Step 2: Add GROUP BY

Now collapse those rows by customer, so each customer appears once with a count of their orders.

SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    COUNT(o.order_id) AS order_count
FROM customers c
INNER JOIN orders o
    ON c.customer_id = o.customer_id
GROUP BY
    c.customer_id,
    c.first_name,
    c.last_name;

Result:

customer_id	first_name	last_name	order_count
1	Priya	Mehta	4
2	Jordan	Ellis	2
3	Sam	Nguyen	1
4	Keiko	Tanaka	2

Notice that first_name and last_name are included in the GROUP BY clause even though they're not what we're really grouping by. This is required in standard SQL — every non-aggregated column in your SELECT must appear in GROUP BY. Since first_name and last_name are functionally dependent on customer_id (one customer has exactly one name), including them in GROUP BY doesn't change the groups at all. It's a formality the database engine requires.

Step 3: Add HAVING

Now apply the filter. We want only customers with more than three orders.

SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    COUNT(o.order_id) AS order_count
FROM customers c
INNER JOIN orders o
    ON c.customer_id = o.customer_id
GROUP BY
    c.customer_id,
    c.first_name,
    c.last_name
HAVING COUNT(o.order_id) > 3;

Result:

customer_id	first_name	last_name	order_count
1	Priya	Mehta	4

Only Priya makes the cut. This is exactly the query structure your manager's question called for.

---

Going Deeper: Joining Three Tables and Calculating Revenue

Let's make the query more useful by also pulling in the order_items table to calculate each customer's total spend. This requires a second JOIN.

SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    COUNT(DISTINCT o.order_id)          AS order_count,
    SUM(oi.quantity * oi.unit_price)    AS total_spend
FROM customers c
INNER JOIN orders o
    ON c.customer_id = o.customer_id
INNER JOIN order_items oi
    ON o.order_id = oi.order_id
GROUP BY
    c.customer_id,
    c.first_name,
    c.last_name
HAVING COUNT(DISTINCT o.order_id) > 1
ORDER BY total_spend DESC;

There's something important in this version: we switched from COUNT(o.order_id) to COUNT(DISTINCT o.order_id). Here's why.

When you join orders to order_items, each order that has multiple items produces multiple rows in the combined dataset. Order 102, for example, might have two items — so it shows up twice after the JOIN. If you just wrote COUNT(o.order_id), you'd count two for that order instead of one. Using DISTINCT inside the COUNT tells the database to count each unique order ID only once, no matter how many item rows it produces.

This is one of the most important subtleties in multi-table aggregation, and it catches even experienced analysts off guard.

The result would look something like:

customer_id	first_name	last_name	order_count	total_spend
1	Priya	Mehta	4	225.99
4	Keiko	Tanaka	2	107.00
2	Jordan	Ellis	2	74.00

---

Using WHERE and HAVING Together

WHERE and HAVING aren't mutually exclusive — you'll often use both in the same query. WHERE filters the raw rows before grouping; HAVING filters the grouped results after.

Say you want the same report, but only for completed orders (not refunded ones):

SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    COUNT(DISTINCT o.order_id)          AS completed_orders,
    SUM(oi.quantity * oi.unit_price)    AS total_spend
FROM customers c
INNER JOIN orders o
    ON c.customer_id = o.customer_id
INNER JOIN order_items oi
    ON o.order_id = oi.order_id
WHERE o.status = 'completed'
GROUP BY
    c.customer_id,
    c.first_name,
    c.last_name
HAVING COUNT(DISTINCT o.order_id) >= 2
ORDER BY total_spend DESC;

The WHERE o.status = 'completed' line removes refunded orders from the dataset entirely before any grouping happens. This means Jordan Ellis, who has one completed order and one refunded order, would only have their one completed order counted. They'd then get filtered out by HAVING because they don't meet the >= 2 threshold.

This is an important pattern: use WHERE to narrow down the rows you want to work with, then use HAVING to filter based on what those rows add up to. For a deeper look at filtering strategies, Advanced SQL Filtering and Sorting covers the WHERE clause and query optimization in much more detail.

---

Filtering Groups After Aggregation: Advanced HAVING Patterns

HAVING can use any aggregate expression — not just COUNT. Here are a few practical patterns you'll encounter in real work.

Filter by minimum average:

-- Cities where the average order value exceeds $50
SELECT
    c.city,
    AVG(oi.quantity * oi.unit_price) AS avg_order_value
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.city
HAVING AVG(oi.quantity * oi.unit_price) > 50;

Filter by total sum:

-- Customers who have spent more than $100 in total
SELECT
    c.first_name,
    c.last_name,
    SUM(oi.quantity * oi.unit_price) AS lifetime_value
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.first_name, c.last_name
HAVING SUM(oi.quantity * oi.unit_price) > 100;

Multiple HAVING conditions:

-- Customers with more than 1 order AND more than $50 total spend
SELECT
    c.first_name,
    c.last_name,
    COUNT(DISTINCT o.order_id)       AS order_count,
    SUM(oi.quantity * oi.unit_price) AS total_spend
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.first_name, c.last_name
HAVING COUNT(DISTINCT o.order_id) > 1
   AND SUM(oi.quantity * oi.unit_price) > 50;

You can chain conditions in HAVING with AND and OR just like in WHERE. For more patterns around HAVING specifically, Filtering Groups After Aggregation: Writing HAVING Clauses That Answer Real Business Questions is a natural next read.

---

Choosing the Right JOIN Type

In all the examples above, we used INNER JOIN — which means only customers who have at least one order appear in the result. That's appropriate when we're reporting on activity.

But if your manager asks "show me all customers, including those who haven't ordered anything, with a count of their orders," you'd need a LEFT JOIN:

SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
GROUP BY
    c.customer_id,
    c.first_name,
    c.last_name;

With LEFT JOIN, a customer with no orders produces a row with NULL in every orders column — and COUNT(o.order_id) counts NULLs as zero. So that customer's order_count would be 0.

---

Hands-On Exercise

Try writing this query yourself before looking at the answer.

The business question: Which cities have customers who have placed a combined total of more than two completed orders? Show the city name, the total number of completed orders from that city, and the total revenue from those orders.

Tables you'll need: customers, orders, order_items Filters: Only completed status orders Grouping: By city HAVING condition: More than two total completed orders

Solution:

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

Based on our sample data, only Chicago (with Priya's four completed orders) would pass the HAVING filter.

Challenge extension: Modify the query to also show the average order value per city, and only include cities where the average order value exceeds $40.

---

Common Mistakes & Troubleshooting

Mistake 1: Using WHERE instead of HAVING to filter on aggregates

-- WRONG: This will throw an error in most databases
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
WHERE COUNT(*) > 3;  -- ❌ Can't use aggregate in WHERE

The fix: use HAVING instead. WHERE runs before GROUP BY, so the aggregate doesn't exist yet.

Mistake 2: Forgetting non-aggregated columns in GROUP BY

-- WRONG in strict SQL mode
SELECT customer_id, first_name, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;  -- ❌ first_name is missing from GROUP BY

Add first_name to GROUP BY, or remove it from SELECT.

Mistake 3: Overcounting due to a JOIN that multiplies rows

This is the COUNT(DISTINCT ...) issue we covered earlier. If you join to a many-side table and your counts seem way too high, inspect the raw JOIN output first — before any GROUP BY. Count the rows and see whether any parent IDs are duplicated. Then add DISTINCT inside your COUNT.

Mistake 4: Applying HAVING when WHERE would be more efficient

This is a performance issue, not a correctness issue. If you're filtering on a non-aggregated column in HAVING, move it to WHERE.

-- Less efficient
HAVING c.city = 'Chicago' AND COUNT(*) > 2

-- Better: filter rows early with WHERE
WHERE c.city = 'Chicago'
...
HAVING COUNT(*) > 2

Filtering with WHERE reduces the number of rows before grouping even starts, which means the database does less work. For large tables, this can be a significant difference. For more on this topic, Master SQL Aggregate Functions: Advanced GROUP BY, HAVING, and Performance Optimization goes deeper into performance considerations.

Mistake 5: Confusion about what the result row represents

After a GROUP BY, each result row no longer represents a single original row — it represents a group of rows. All the columns you see are either group-defining columns (from GROUP BY) or aggregated summaries. Trying to also show individual order details in the same query won't work; you'd need a subquery or a window function for that.

---

Summary & Next Steps

You now have the core pattern for multi-table aggregation with filtering. To recap what we covered:

• JOIN assembles your data across tables. Run it first and verify the shape is correct.
• GROUP BY collapses the joined rows into one row per group, with every non-aggregated column listed in the GROUP BY clause.
• HAVING filters those groups based on aggregate expressions — always after grouping, never before.
• WHERE and HAVING can coexist in the same query, each filtering at a different stage.
• COUNT(DISTINCT column) protects you from overcounting when a JOIN multiplies rows.

This pattern — JOIN → GROUP BY → HAVING — is the backbone of most business reporting queries. Once you're fluent in it, you're ready for the next level of complexity.

Where to go next:

• Multi-Table Reporting with JOIN and GROUP BY builds on exactly this pattern with more realistic reporting scenarios.
• Combining Aggregates with Conditional Logic: GROUP BY, HAVING, and CASE WHEN shows you how to add conditional columns to your grouped output.
• When your queries grow more complex, Writing Multi-Step Analytical Queries will show you how to chain these building blocks into sophisticated analyses.

Practice is the key. Take any dataset you have access to — sales records, app usage logs, survey responses — and challenge yourself to answer a question that requires joining two tables and filtering on an aggregate. The pattern will become second nature faster than you think.