SQL HAVING Clause

Gaurav Shegekar from ByteCraftOct 23, 2026

SQL HAVING Clause

Filter Groups, Not Individual Rows

Oct 23

Imagine you’re building an e-commerce application. Your manager asks you to find all customers who have spent more than ₹10,000 on orders. Your database contains thousands of orders, and each customer may have placed multiple orders. You can’t simply check one order at a time because the question is about the customer’s total spending, not the amount of an individual order.

This is where SQL’s HAVING clause becomes useful. It allows you to filter groups after calculating values such as total spending, number of orders, or average purchase amount. If you’ve already learned GROUP BY, you’re ready to understand one of the most important concepts used in SQL reports, dashboards, and backend interviews.

What Is the HAVING Clause?

The HAVING clause filters groups created by GROUP BY. While WHERE filters individual rows before grouping, HAVING checks the result of a group after SQL calculates its aggregate values. This makes it useful when your condition depends on SUM(), COUNT(), AVG(), or another aggregate function.

Let’s use an orders table for our examples.

Customer 1 has placed three orders worth ₹4,000, ₹3,500, and ₹4,000. Customer 2 has placed two orders worth ₹2,500 and ₹3,000, while customer 3 has placed one order worth ₹7,000. If we want customers whose total spending exceeds ₹10,000, we need to group orders by customer and calculate the total for each group.

1. Your First HAVING Query

Let’s begin with the exact problem we want to solve: find customers whose combined order amount exceeds ₹10,000. We can use GROUP BY to collect each customer’s orders and SUM() to calculate their total spending. Then HAVING removes the groups that don’t meet our condition.

SELECT
    customer_id,
    SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 10000;

The result is:

customer_id : 1 total_spent : 11500

Customer 1 qualifies because their total is ₹11,500. Customer 2’s total is ₹5,500, and customer 3’s total is ₹7,000, so neither appears in the result. Notice that the condition checks the sum of all orders belonging to a customer, not the amount of any single order.

2. WHERE vs. HAVING: What’s the Difference?

This is one of the most common SQL interview questions, and the easiest way to remember the answer is to think about what you’re filtering. WHERE decides which individual rows should participate in the query, while HAVING decides which groups should remain after aggregation. They can appear in the same query because they solve different problems.

Suppose your manager wants customers whose spending on orders worth at least ₹3,000 exceeds ₹7,000. First, exclude orders below ₹3,000, then calculate the remaining total for each customer, and finally keep only customers whose total exceeds ₹7,000.

SELECT
    customer_id,
    SUM(amount) AS total_spent
FROM orders
WHERE amount >= 3000
GROUP BY customer_id
HAVING SUM(amount) > 7000;

Here, WHERE amount >= 3000 filters individual orders before grouping. GROUP BY customer_id creates a group for each customer, and HAVING SUM(amount) > 7000 filters those groups based on their calculated totals. In this example, customer 1 qualifies with ₹11,500, while customer 3’s ₹7,000 does not exceed the threshold.

Remember this simple rule: WHERE filters rows; HAVING filters groups.

3. Use HAVING with COUNT()

HAVING isn’t limited to money-related calculations. Imagine you’re building a dashboard and want to identify customers who have placed at least two orders. You don’t care whether a customer has spent ₹1,000 or ₹10,000; you care about how many orders they have placed.

SELECT
    customer_id,
    COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2;

This query returns customer 1, who has three orders, and customer 2, who has two orders. Customer 3 is excluded because they have only one order. Similar queries are useful for finding customers with repeated purchases, products that appear in multiple transactions, or users who have submitted several support tickets.

4. Use HAVING with AVG(), MIN(), and MAX()

You can also use HAVING to filter groups based on average, minimum, or maximum values. For example, a business might want to identify customers whose average order amount is above ₹3,000. Rather than examining each order separately, SQL calculates an average for every customer and keeps only the qualifying groups.

SELECT
    customer_id,
    AVG(amount) AS average_order_amount
FROM orders
GROUP BY customer_id
HAVING AVG(amount) > 3000;

Customer 1 qualifies because their average order amount is approximately ₹3,833.33. Customer 2’s average is ₹2,750, while customer 3’s average is ₹7,000, so customers 1 and 3 appear in the result. You could similarly use MAX(amount) to find customers who have placed an order above a particular value, or MIN(amount) to filter groups based on their smallest order.

5. Combine WHERE, GROUP BY, and HAVING

Real queries often use several SQL clauses together. Imagine you want to find customers who have spent more than ₹5,000 on orders placed in October. The database must first select orders from October, group those orders by customer, calculate the total for each customer, and then keep groups whose total exceeds ₹5,000.

SELECT
    customer_id,
    SUM(amount) AS october_spending
FROM orders
WHERE order_date >= '2026-10-01'
  AND order_date < '2026-11-01'
GROUP BY customer_id
HAVING SUM(amount) > 5000
ORDER BY october_spending DESC;

This example assumes the table has an order_date column and uses a half-open date range, which is useful when the column contains timestamps as well as dates. The query filters the input rows by date, calculates spending per customer, removes customers whose totals are too low, and sorts the remaining results from highest to lowest spending. It’s a realistic pattern for monthly reports and admin dashboards.

6. Can You Use HAVING Without GROUP BY?

Yes. Some SQL databases allow HAVING without an explicit GROUP BY. When aggregate functions are present, the query can treat the selected rows as one group and decide whether that group should appear in the result. This is less common in everyday beginner queries, but it’s useful to know that HAVING isn’t always required to follow a written GROUP BY clause.

For example:

SELECT SUM(amount) AS total_revenue
FROM orders
HAVING SUM(amount) > 20000;

This query returns the total revenue if it exceeds ₹20,000; otherwise, it returns no rows. If you only need the overall total without a condition, you can remove HAVING. For most grouped reports, however, you’ll use HAVING alongside GROUP BY to filter individual groups based on aggregate calculations.

The Takeaway

The HAVING clause becomes powerful when your question is about a group’s result rather than an individual database record. Use it to find customers whose spending exceeds a threshold, categories with enough orders, or groups whose average values meet a requirement. Pair it with GROUP BY and aggregate functions to turn raw rows into useful business information.

For Java backend developers, understanding this distinction helps when writing reporting queries, working with Spring Data JPA, and preparing for SQL interviews. You don’t need to fetch every order into Java just to calculate a total and discard customers who don’t meet a condition. Start by deciding whether your condition applies to individual rows or aggregated groups, and then choose WHERE or HAVING accordingly.

Here’s a question for you: If you need customers who have placed more than five orders, would you use WHERE COUNT(*) > 5 or HAVING COUNT(*) > 5? And why?

Share your answer in the comments. If you’ve ever confused WHERE and HAVING while solving a SQL problem, you’re definitely not alone.

Follow ByteCraft for practical SQL, Java, and backend development lessons.

Thanks for reading ByteCraft! Subscribe for free to receive new posts and support my work.

Pledge your support

ByteCraft is free today. But if you enjoyed this post, you can tell ByteCraft that their writing is valuable by pledging a future subscription. You won't be charged unless they enable payments.

Pledge your support