Learn how to write production-quality SQL that combines GROUP BY, HAVING, and CASE WHEN in a single query. This lesson goes beyond the basics to cover conditional counts, rate calculations, performance tier classification, and the subtle bugs that cause silently wrong results.
Picture this: your manager asks for a report showing which sales reps hit their quarterly targets, broken down by product category, with a flag for anyone whose return rate exceeded 10%. You know how to run a GROUP BY. You know what CASE WHEN does in a SELECT. But the moment you try to combine them — filtering aggregated results while simultaneously pivoting conditional counts into columns — the query starts fighting back. Column references break. WHERE clauses reject aggregate functions. The output looks right until you check it against the raw data and realize something slipped.
This is the lesson that untangles all of that. We're going to go deep on how GROUP BY, HAVING, and CASE WHEN work together, not just individually. By the end, you'll be writing production-quality queries that aggregate conditional logic across multiple dimensions, filter those aggregates precisely, and do it in a way that's readable enough for a colleague to maintain six months from now.
What you'll learn:
HAVING differs from WHERE and when each one belongs in your queryCASE WHEN inside aggregate functions to create conditional counts and sumsYou should already be comfortable with basic SELECT, FROM, WHERE, and GROUP BY syntax. If any of those feel shaky, SQL Basics: Master SELECT, FROM, WHERE Clauses and Build Your First Queries and Grouping and Summarizing Data: COUNT, SUM, AVG, and GROUP BY for Beginners will bring you up to speed. You should also have a basic grasp of CASE WHEN — Writing SQL CASE Expressions: Conditional Logic Inside SELECT, WHERE, and GROUP By covers the fundamentals well.
All examples in this lesson use a realistic e-commerce schema. Here it is so you can follow along:
-- Customers
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
full_name VARCHAR(100),
region VARCHAR(50),
signup_date DATE
);
-- Orders
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
rep_id INT,
order_date DATE,
status VARCHAR(20), -- 'completed', 'returned', 'cancelled'
total_amount DECIMAL(10,2)
);
-- Order line items
CREATE TABLE order_items (
item_id INT PRIMARY KEY,
order_id INT,
product_id INT,
category VARCHAR(50),
quantity INT,
unit_price DECIMAL(10,2)
);
-- Sales reps
CREATE TABLE sales_reps (
rep_id INT PRIMARY KEY,
rep_name VARCHAR(100),
territory VARCHAR(50)
);
Most SQL practitioners know the textbook answer: WHERE filters rows before grouping, HAVING filters groups after aggregation. What they often miss is why that distinction produces different results in practice, and how easily you can use the wrong one and get a plausible-looking but incorrect output.
Consider this scenario: you want to find customers who placed more than 5 orders, but only counting completed orders (not returns or cancellations). Here's the wrong approach first:
-- WRONG: filters rows first, then counts
SELECT
customer_id,
COUNT(*) AS completed_orders
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) > 5;
Wait — is that actually wrong? In this specific case, no. You do want to pre-filter to completed orders. The WHERE clause excludes returned and cancelled rows before the count runs, which is correct here.
But now the requirements shift slightly: you want customers who placed more than 5 total orders, where at least 3 were completed. Now watch what breaks:
-- WRONG: this only counts completed orders, not total orders
SELECT
customer_id,
COUNT(*) AS total_orders
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) > 5;
The WHERE clause stripped out non-completed orders before aggregation, so COUNT(*) no longer represents total orders — it represents completed orders only. The HAVING COUNT(*) > 5 is checking the same filtered count. You'll never know unless you cross-reference against raw data.
Here's the correct approach:
-- CORRECT: count all rows, then filter groups using aggregated conditions
SELECT
customer_id,
COUNT(*) AS total_orders,
COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_orders
FROM orders
GROUP BY customer_id
HAVING
COUNT(*) > 5
AND COUNT(CASE WHEN status = 'completed' THEN 1 END) >= 3;
This query keeps all rows in the aggregation, uses conditional logic inside COUNT to count only completed orders, and then applies both conditions in HAVING against those correctly computed aggregates.
Key insight
WHERE changes what gets counted. HAVING changes which groups survive after counting. When your question is "how many of X among all Y," you almost always need the conditional logic inside the aggregate, not in WHERE.
This is one of the most powerful — and underused — patterns in SQL. Instead of writing multiple subqueries or joining back to filtered versions of a table, you can embed conditions directly inside SUM, COUNT, AVG, and MAX.
-- COUNT(CASE WHEN ... THEN 1 END) counts only rows where the condition is true
SELECT
rep_id,
COUNT(*) AS total_orders,
COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed,
COUNT(CASE WHEN status = 'returned' THEN 1 END) AS returned,
COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled
FROM orders
GROUP BY rep_id;
This is a manual pivot — you're turning row values into columns within a single query pass. The trick is that CASE WHEN returns 1 when the condition is true and NULL when it isn't, and COUNT ignores NULL values. So you're counting only the matching rows.
Tip
COUNT(CASE WHEN condition THEN 1 END) and SUM(CASE WHEN condition THEN 1 ELSE 0 END) produce identical results, but they behave differently if you change the aggregation. SUM is better when you want to add values other than 1. COUNT is safer when you just want to tally.
Now let's go further. Your finance team wants to know the revenue breakdown by rep: total revenue, revenue from completed orders only, and total refunded amount (returned orders):
SELECT
r.rep_name,
COUNT(o.order_id) AS total_orders,
SUM(o.total_amount) AS gross_revenue,
SUM(CASE WHEN o.status = 'completed' THEN o.total_amount ELSE 0 END) AS completed_revenue,
SUM(CASE WHEN o.status = 'returned' THEN o.total_amount ELSE 0 END) AS refunded_amount,
ROUND(
100.0 * COUNT(CASE WHEN o.status = 'returned' THEN 1 END)
/ NULLIF(COUNT(o.order_id), 0),
1
) AS return_rate_pct
FROM sales_reps r
LEFT JOIN orders o ON r.rep_id = o.rep_id
GROUP BY r.rep_id, r.rep_name
ORDER BY completed_revenue DESC;
A few things worth unpacking here:
LEFT JOIN so reps with zero orders still appear in the results. If you used INNER JOIN, reps who haven't made a sale would disappear silently.NULLIF(COUNT(o.order_id), 0) prevents a division-by-zero error for reps with no orders. This is discussed thoroughly in NULL Handling in SQL: IS NULL, COALESCE, and NULLIF.return_rate_pct calculation lives inside a ROUND() call, which is fine — you can nest scalar functions around your aggregates freely.Warning
When you use a LEFT JOIN and then aggregate, make sure you're aggregating the right table's columns. COUNT(*) will count all rows including the join-produced NULLs. Use COUNT(o.order_id) instead, which counts only non-NULL values from the orders table — meaning reps with no orders correctly get 0.
Now we push this further. You don't just want to see the return rate — you want to filter to reps whose return rate exceeds 10% and who processed at least 20 orders. That filter has to live in HAVING, and it has to repeat the aggregate expression:
SELECT
r.rep_name,
COUNT(o.order_id) AS total_orders,
SUM(CASE WHEN o.status = 'completed' THEN o.total_amount ELSE 0 END) AS completed_revenue,
ROUND(
100.0 * COUNT(CASE WHEN o.status = 'returned' THEN 1 END)
/ NULLIF(COUNT(o.order_id), 0),
1
) AS return_rate_pct
FROM sales_reps r
LEFT JOIN orders o ON r.rep_id = o.rep_id
GROUP BY r.rep_id, r.rep_name
HAVING
COUNT(o.order_id) >= 20
AND (
100.0 * COUNT(CASE WHEN o.status = 'returned' THEN 1 END)
/ NULLIF(COUNT(o.order_id), 0)
) > 10;
Notice that in the HAVING clause, you can't reference the alias return_rate_pct — aliases defined in SELECT are not visible to HAVING in most databases (PostgreSQL, MySQL pre-8.0, SQL Server). You have to write the expression again.
Note
MySQL 8.0+ and some databases like DuckDB allow referencing SELECT aliases in HAVING. But it's safer to repeat the expression for portability, or use a CTE to wrap the aggregation and filter in an outer query. The CTE approach is cleaner for complex expressions — see Common Table Expressions (CTEs) for Cleaner SQL for the pattern.
If repeating the expression bothers you (it should — it's a maintenance hazard), the CTE version looks like this:
WITH rep_metrics AS (
SELECT
r.rep_name,
COUNT(o.order_id) AS total_orders,
SUM(CASE WHEN o.status = 'completed' THEN o.total_amount ELSE 0 END) AS completed_revenue,
ROUND(
100.0 * COUNT(CASE WHEN o.status = 'returned' THEN 1 END)
/ NULLIF(COUNT(o.order_id), 0),
1
) AS return_rate_pct
FROM sales_reps r
LEFT JOIN orders o ON r.rep_id = o.rep_id
GROUP BY r.rep_id, r.rep_name
)
SELECT *
FROM rep_metrics
WHERE total_orders >= 20
AND return_rate_pct > 10
ORDER BY return_rate_pct DESC;
The outer WHERE operates on the CTE's output, which is already aggregated. This is functionally equivalent to the HAVING approach but far easier to read and modify.
Here's a scenario you'll hit constantly: after aggregating, you want to assign a category or tier to each group based on its aggregate values. Think performance tiers for sales reps, risk buckets for customers, or size classifications for orders.
You can do this with CASE WHEN in the SELECT referencing your aggregate — but since SELECT aliases aren't available within the same SELECT level, you nest the logic inside the expression itself:
SELECT
r.rep_name,
r.territory,
SUM(CASE WHEN o.status = 'completed' THEN o.total_amount ELSE 0 END) AS completed_revenue,
COUNT(o.order_id) AS total_orders,
CASE
WHEN SUM(CASE WHEN o.status = 'completed' THEN o.total_amount ELSE 0 END) >= 100000 THEN 'Platinum'
WHEN SUM(CASE WHEN o.status = 'completed' THEN o.total_amount ELSE 0 END) >= 50000 THEN 'Gold'
WHEN SUM(CASE WHEN o.status = 'completed' THEN o.total_amount ELSE 0 END) >= 20000 THEN 'Silver'
ELSE 'Bronze'
END AS performance_tier
FROM sales_reps r
LEFT JOIN orders o ON r.rep_id = o.rep_id
AND o.order_date >= DATE_TRUNC('quarter', CURRENT_DATE)
GROUP BY r.rep_id, r.rep_name, r.territory
ORDER BY completed_revenue DESC;
This query also demonstrates an important technique: the AND o.order_date >= ... condition is on the JOIN clause, not in a WHERE clause. For a LEFT JOIN, this matters. If you put it in WHERE, you implicitly convert the LEFT JOIN to an INNER JOIN because you're excluding rows where the order date is NULL (which is what a LEFT JOIN produces when there's no match). Keeping date filters on the join condition preserves reps with no orders this quarter.
Key insight
When filtering on columns from the right-hand table of a LEFT JOIN, the filter belongs in the ON clause, not WHERE. Moving it to WHERE silently turns your outer join into an inner join. This is one of the most common subtle bugs in reporting queries.
Now you can filter by tier too. Want only Silver and Gold reps? Wrap it in a CTE and filter:
WITH rep_performance AS (
SELECT
r.rep_name,
r.territory,
SUM(CASE WHEN o.status = 'completed' THEN o.total_amount ELSE 0 END) AS completed_revenue,
COUNT(o.order_id) AS total_orders,
CASE
WHEN SUM(CASE WHEN o.status = 'completed' THEN o.total_amount ELSE 0 END) >= 100000 THEN 'Platinum'
WHEN SUM(CASE WHEN o.status = 'completed' THEN o.total_amount ELSE 0 END) >= 50000 THEN 'Gold'
WHEN SUM(CASE WHEN o.status = 'completed' THEN o.total_amount ELSE 0 END) >= 20000 THEN 'Silver'
ELSE 'Bronze'
END AS performance_tier
FROM sales_reps r
LEFT JOIN orders o ON r.rep_id = o.rep_id
AND o.order_date >= DATE_TRUNC('quarter', CURRENT_DATE)
GROUP BY r.rep_id, r.rep_name, r.territory
)
SELECT *
FROM rep_performance
WHERE performance_tier IN ('Silver', 'Gold')
ORDER BY completed_revenue DESC;
Let's build something more realistic. Your product team wants a report that shows, for each product category:
This requires joining three tables and layering conditional logic across multiple aggregate expressions:
SELECT
oi.category,
COUNT(DISTINCT o.order_id) AS total_orders,
SUM(oi.quantity * oi.unit_price) AS gross_revenue,
SUM(
CASE WHEN o.status = 'completed'
THEN oi.quantity * oi.unit_price
ELSE 0 END
) AS completed_revenue,
SUM(
CASE WHEN o.status = 'returned'
THEN oi.quantity * oi.unit_price
ELSE 0 END
) AS returned_revenue,
ROUND(
SUM(CASE WHEN o.status = 'completed' THEN oi.quantity * oi.unit_price ELSE 0 END)
/ NULLIF(COUNT(DISTINCT CASE WHEN o.status = 'completed' THEN o.order_id END), 0),
2
) AS avg_completed_order_value,
ROUND(
100.0
* COUNT(DISTINCT CASE WHEN o.status = 'returned' THEN o.order_id END)
/ NULLIF(COUNT(DISTINCT o.order_id), 0),
1
) AS return_rate_pct,
CASE
WHEN 100.0
* COUNT(DISTINCT CASE WHEN o.status = 'returned' THEN o.order_id END)
/ NULLIF(COUNT(DISTINCT o.order_id), 0) > 15
THEN 'High Return Risk'
ELSE 'Normal'
END AS risk_flag
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY oi.category
ORDER BY gross_revenue DESC;
A few things to notice here:
COUNT(DISTINCT o.order_id) rather than COUNT(*) because each order can have multiple line items. If we used COUNT(*), we'd count each line item as a separate order.COUNT(DISTINCT CASE WHEN o.status = 'returned' THEN o.order_id END) counts distinct returned orders — the CASE WHEN returns NULL for non-returned orders, and COUNT(DISTINCT ...) ignores NULLs.risk_flag repeats itself — this is unavoidable at the raw SQL level. A CTE would clean this up.This is the kind of query that gets built once and runs daily in a BI dashboard. Getting the logic right upfront matters.
Tip
When joining a header table (like orders) to a line-item table (like order_items), always think carefully about your unit of counting. Are you counting orders or line items? COUNT(DISTINCT order_id) vs COUNT(*) is usually the decision point. If in doubt, compare your output against a simple SELECT COUNT(DISTINCT order_id) FROM orders and verify the numbers match what you expect.
Let's build something you could actually hand off. The goal: a single query that powers a quarterly sales dashboard, showing per-rep metrics, categorized performance, and flagging outliers — all in one result set.
WITH quarterly_orders AS (
-- Scope to current quarter only, preserving all reps via LEFT JOIN logic
SELECT
o.order_id,
o.rep_id,
o.status,
o.total_amount,
o.order_date
FROM orders o
WHERE o.order_date >= DATE_TRUNC('quarter', CURRENT_DATE)
AND o.order_date < DATE_TRUNC('quarter', CURRENT_DATE) + INTERVAL '3 months'
),
rep_metrics AS (
SELECT
r.rep_id,
r.rep_name,
r.territory,
COUNT(qo.order_id) AS total_orders,
COUNT(CASE WHEN qo.status = 'completed' THEN 1 END) AS completed_orders,
COUNT(CASE WHEN qo.status = 'returned' THEN 1 END) AS returned_orders,
COUNT(CASE WHEN qo.status = 'cancelled' THEN 1 END) AS cancelled_orders,
COALESCE(SUM(CASE WHEN qo.status = 'completed'
THEN qo.total_amount END), 0) AS completed_revenue,
COALESCE(SUM(CASE WHEN qo.status = 'returned'
THEN qo.total_amount END), 0) AS refunded_amount,
ROUND(
100.0 * COUNT(CASE WHEN qo.status = 'returned' THEN 1 END)
/ NULLIF(COUNT(qo.order_id), 0),
1
) AS return_rate_pct,
ROUND(
COALESCE(SUM(CASE WHEN qo.status = 'completed' THEN qo.total_amount END), 0)
/ NULLIF(COUNT(CASE WHEN qo.status = 'completed' THEN 1 END), 0),
2
) AS avg_order_value
FROM sales_reps r
LEFT JOIN quarterly_orders qo ON r.rep_id = qo.rep_id
GROUP BY r.rep_id, r.rep_name, r.territory
),
rep_classified AS (
SELECT
*,
CASE
WHEN completed_revenue >= 100000 THEN 'Platinum'
WHEN completed_revenue >= 50000 THEN 'Gold'
WHEN completed_revenue >= 20000 THEN 'Silver'
WHEN total_orders > 0 THEN 'Bronze'
ELSE 'Inactive'
END AS performance_tier,
CASE
WHEN return_rate_pct > 15 AND total_orders >= 10 THEN 'Flagged'
ELSE 'OK'
END AS return_flag
FROM rep_metrics
)
SELECT
rep_name,
territory,
total_orders,
completed_orders,
returned_orders,
completed_revenue,
refunded_amount,
return_rate_pct,
avg_order_value,
performance_tier,
return_flag
FROM rep_classified
WHERE total_orders > 0 -- exclude truly inactive reps if desired
ORDER BY
performance_tier, -- alphabetical here; for custom order, use CASE
completed_revenue DESC;
This structure is worth studying:
quarterly_orders): Scopes the date range in one place. If you ever need to change the time window, you change it once.rep_metrics): All the heavy aggregation happens here. No classification logic yet — just numbers.rep_classified): Applies business rules to the numbers. This layer is easy to modify when thresholds change.This separation of concerns — aggregate, then classify, then filter and present — is the pattern that makes complex reporting queries maintainable over time. For an even deeper dive into structuring multi-step queries with CTEs, see Advanced Subqueries and CTEs: Mastering Complex SQL Query Architecture.
Work through these in order. Each builds on the previous.
Setup: Use the schema defined at the top of this lesson. Populate it with realistic-looking data — or adapt these exercises to any orders/transactions table you have access to.
Exercise 1 — Basic conditional aggregation:
Write a query that shows, for each customer region, the total number of orders, the number of completed orders, and the number of returned orders. Add a column completion_rate_pct showing the percentage of orders that were completed.
Exercise 2 — HAVING with conditional aggregate: Extend your query from Exercise 1 to show only regions where the return rate exceeds 8% AND the total order count is at least 50.
Exercise 3 — Tier classification: Write a query that classifies each region as 'High Volume' (500+ orders), 'Medium Volume' (100-499 orders), or 'Low Volume' (under 100 orders), and shows the average order value for each tier. Use a CTE to compute the aggregates first, then apply the classification in a second CTE.
Exercise 4 — Multi-table conditional aggregation:
Join orders to order_items and produce a report by category that shows:
Stretch goal: Modify Exercise 4 to also break out the revenue by territory (by joining sales_reps), keeping category as the primary grouping dimension.
-- WRONG
SELECT rep_id, COUNT(*) AS order_count
FROM orders
WHERE COUNT(*) > 10 -- Error: aggregate functions not allowed in WHERE
GROUP BY rep_id;
The fix: move aggregate conditions to HAVING. WHERE runs before grouping — it literally doesn't have access to aggregated values yet.
-- May fail in PostgreSQL, SQL Server
SELECT rep_id, COUNT(*) AS order_count
FROM orders
GROUP BY rep_id
HAVING order_count > 10; -- alias not available in most databases
Fix: repeat the expression in HAVING, or use a CTE and filter in WHERE.
-- These are NOT equivalent when used in SUM
SUM(CASE WHEN status = 'completed' THEN total_amount END) -- NULL for non-completed
SUM(CASE WHEN status = 'completed' THEN total_amount ELSE 0 END) -- 0 for non-completed
For SUM, both produce the same result because SUM ignores NULLs. But if you then divide by this sum, a NULL result from the first pattern will propagate through your arithmetic in ways that 0 won't. The ELSE 0 pattern is safer when the expression is used in subsequent calculations.
-- WRONG: implicitly makes this an inner join
SELECT r.rep_name, COUNT(o.order_id)
FROM sales_reps r
LEFT JOIN orders o ON r.rep_id = o.rep_id
WHERE o.order_date >= '2024-01-01' -- reps with no orders have NULL order_date, excluded here
GROUP BY r.rep_id, r.rep_name;
-- CORRECT: filter on the JOIN condition
SELECT r.rep_name, COUNT(o.order_id)
FROM sales_reps r
LEFT JOIN orders o ON r.rep_id = o.rep_id
AND o.order_date >= '2024-01-01'
GROUP BY r.rep_id, r.rep_name;
When joining one-to-many relationships (orders to line items), COUNT(*) counts line items, not orders. Always ask: "What is one row in my FROM clause representing?" and use COUNT(DISTINCT order_id) when you need order-level counts.
Any time you compute a rate, protect against zero denominators:
-- Will error if COUNT is 0
COUNT(CASE WHEN status = 'returned' THEN 1 END) / COUNT(*)
-- Safe version
COUNT(CASE WHEN status = 'returned' THEN 1 END) / NULLIF(COUNT(*), 0)
NULLIF(expr, 0) returns NULL when the denominator is zero, which propagates cleanly through calculations instead of throwing an error. See more on this pattern in NULL Handling in SQL: IS NULL, COALESCE, and NULLIF.
Conditional aggregation is generally efficient because it makes a single pass through the data. The alternative — multiple subqueries or self-joins for each condition — is almost always slower and harder to read. That said, a few things to keep in mind:
WHERE, do it. Only use HAVING for conditions that genuinely require the aggregated value.WHERE or in a CTE that runs first. This limits the rows that flow into the aggregation.WHERE YEAR(order_date) = 2024 prevents index use. Prefer WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'.For deeper work on query optimization, SQL Query Optimization: Reading Execution Plans - Advanced Performance Analysis will show you how to understand what the database engine is actually doing with these queries.
Tip
If you find yourself writing the same conditional aggregate expression five or six times across a large query, consider whether a CTE or subquery that materializes the aggregation first would be cleaner. Some databases (like Redshift and BigQuery) benefit significantly from aggregating early and joining the result, rather than aggregating after a large join.
The patterns in this lesson sit at the intersection of aggregation and transformation — you're not just summarizing data, you're reshaping its meaning through conditional logic applied during the summarization process. Once this clicks, several adjacent techniques open up naturally.
Conditional Aggregation with CASE WHEN: Pivoting Logic Without Reshaping Your Data goes deeper on using conditional aggregation to pivot row data into columns — the same fundamental technique, pushed further.
Multi-Table Reporting with JOIN and GROUP BY: Aggregating Across Relationships in a Single Query builds on the multi-table patterns here with more complex join scenarios.
And when your aggregations start to feel repetitive across multiple time windows or dimensions, Window Functions: RANK, ROW_NUMBER, and LAG introduces a whole different aggregation model that works alongside GROUP BY rather than replacing it.
You've worked through the full stack of combining aggregates with conditional logic. Let's recap the key moves:
WHERE filters rows before aggregation; HAVING filters groups after. Use HAVING whenever your condition depends on an aggregate value.CASE WHEN inside aggregate functions — SUM(CASE WHEN ... END) and COUNT(CASE WHEN ... END) — lets you compute multiple conditional metrics in a single query pass without subqueries.LEFT JOIN semantics break when you filter the right table in WHERE — move those filters to the ON clause.NULLIF protects rate calculations from division-by-zero errors.COUNT(DISTINCT ...) is your safeguard against overcounting when joining one-to-many relationships.From here, the natural next step is pushing these patterns into more complex reporting scenarios. Writing Efficient SQL Aggregations: GROUP BY, HAVING, and Grouping Sets Explained introduces GROUPING SETS, ROLLUP, and CUBE — SQL features that let you compute multiple levels of aggregation in a single query, which pairs perfectly with the conditional aggregation techniques you just learned.