Wicked Smart Data
LearnInsightsAboutContact
Sign InLet's Build
LearnInsightsAboutContact
Sign InLet's Build
Wicked Smart Data

Intelligence, automation, and expert execution — plus an elite library of free knowledge. We turn complexity into competitive advantage.

Start a conversation

Platform

  • Learning Paths
  • Insights
  • RSS Feed

Company

  • About
  • Contact
  • Work With Us

Legal

  • Privacy Policy
  • Terms of Service

© 2026 Wicked Smart Data. All rights reserved.

Intelligence · Automation · Advantage

All Insights
SQL

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

Learn how to write SQL queries that join multiple tables, group the results, and filter on aggregated values using HAVING. This lesson walks you through the full pattern step by step, with realistic examples and common mistakes to avoid.

🌱 Foundation16 min readOct 3, 2026Updated Oct 3, 2026
Filtering Aggregated Results Across Multiple Tables: Combining JOIN, GROUP BY, and HAVING in One Query
On this page
  • Introduction
  • Prerequisites
  • The Scenario: An E-Commerce Database
  • How the Three Clauses Work Together
  • Building the Query Piece by Piece
  • Step 1: The JOIN
  • Step 2: Add GROUP BY
  • Step 3: Add HAVING
  • Going Deeper: Joining Three Tables and Calculating Revenue
  • Using WHERE and HAVING Together
  • Filtering Groups After Aggregation: Advanced HAVING Patterns
  • Choosing the Right JOIN Type
  • Hands-On Exercise
  • Common Mistakes & Troubleshooting
  • Summary & Next Steps

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.

Key insight

Think of it this way — WHERE is a bouncer at the door who checks IDs before anyone gets in. HAVING is a manager who looks at the final party roster and says "any group with fewer than 10 people doesn't get a table." They both filter, but at completely different stages.

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.

Tip

Using table aliases makes multi-table queries much more readable. Instead of writing customers.customer_id everywhere, you can write FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id. For more on this technique, see Using SQL Aliases Effectively.

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.

Warning

A common beginner mistake is writing GROUP BY customer_id and then selecting first_name without including it in GROUP BY. Some databases (like MySQL with permissive settings) will allow this and return an arbitrary name for each group. Others (PostgreSQL, standard SQL) will throw an error. Always include every non-aggregated SELECT column in your GROUP BY.

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.

Note

You'll notice we write HAVING COUNT(o.order_id) > 3 and not HAVING order_count > 3. That's because order_count is an alias we created in SELECT, and SELECT runs after HAVING. Most databases won't recognize the alias at this stage. Some modern databases (like BigQuery or DuckDB) do support this as an extension, but for portable, reliable SQL, repeat the aggregate expression.


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.

Key insight

When you JOIN to a "child" table (one where each parent row can have multiple children, like orders to order_items), your row count multiplies. Always check whether COUNT(*) or a bare COUNT(column) will overcount, and use COUNT(DISTINCT column) when you need to count parent-level entities.

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.

Warning

If you use LEFT JOIN but then add a HAVING COUNT(o.order_id) > 0, you're effectively converting it back to an INNER JOIN — you're filtering out all the zero-count rows. That's sometimes what you want, but make sure it's intentional. Understanding when to use INNER vs. LEFT vs. FULL OUTER JOIN matters a lot for aggregation accuracy.


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.

Tip

If your results look strange, add intermediate steps. Comment out the GROUP BY and HAVING, look at the raw JOIN output, and ask yourself: "does this joined dataset look right?" Then add GROUP BY, verify the groups, then add HAVING. Building and checking layer by layer catches problems before they compound. Debugging SQL Queries is an excellent companion resource for this process.


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.

Work With Us

From insight to implementation

Reading is the start. When you're ready to build the data, automation, or AI systems behind it, our team turns strategy into shipped results.

Let's Build

SQL Fundamentals

Previous

Building a Complete Analytical Query from Scratch: Combining SELECT, JOIN, GROUP BY, and Subqueries to Answer a Multi-Part Business Question

Next

Using SQL to Compare Current and Prior Period Results: Date Filtering, Self-Joins, and Conditional Aggregation in Practice

Related Insights

SQLPractitioner

Writing SQL to Rank and Deduplicate Rows Using ROW_NUMBER in a CTE: Partition, Filter, and Delete Duplicates in a Single Query

23 min
SQLExpert

Writing SQL to Detect Missing Foreign Key References: Using LEFT JOIN and NOT EXISTS to Audit Referential Integrity Without Constraints

31 min
SQLFoundation

Understanding Window Function Partitioning: How PARTITION BY Divides Data and When to Use It Without ORDER BY

16 min

On this page

  • Introduction
  • Prerequisites
  • The Scenario: An E-Commerce Database
  • How the Three Clauses Work Together
  • Building the Query Piece by Piece
  • Step 1: The JOIN
  • Step 2: Add GROUP BY
  • Step 3: Add HAVING
  • Going Deeper: Joining Three Tables and Calculating Revenue
  • Using WHERE and HAVING Together
  • Filtering Groups After Aggregation: Advanced HAVING Patterns
  • Choosing the Right JOIN Type
  • Hands-On Exercise
  • Common Mistakes & Troubleshooting
  • Summary & Next Steps