SQL HAVING Clause – Examples, GROUP BY Filters, and WHERE Differences | DbSchema

Table of Contents

1. What the SQL HAVING clause does
2. SQL HAVING syntax
3. HAVING vs WHERE
4. SQL HAVING examples with COUNT, SUM, and AVG
5. HAVING with multiple conditions
6. HAVING with COUNT DISTINCT
7. HAVING with JOINs
8. Can you use HAVING without GROUP BY?
9. Aliases, portability, and database tips
10. Common mistakes and optimization tips
11. Use HAVING in DbSchema
12. FAQ
13. Conclusion

The SQL HAVING clause filters grouped results after aggregation. If WHERE filters individual rows, HAVING filters groups such as "customers with more than five orders" or "departments whose average salary is above 50,000".

That makes HAVING essential for reporting, analytics, dashboards, and quality checks. It is one of the clauses people learn early, but it becomes much more powerful once you combine it with GROUP BY, joins, COUNT(DISTINCT ...), and multiple conditions.

What the SQL HAVING clause does

Use HAVING when your filter depends on an aggregate expression such as:

• COUNT(*)
• SUM(amount)
• AVG(score)
• MIN(created_at)
• MAX(order_total)

Example:

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

This query does not filter rows before grouping. It groups rows first, counts them, then keeps only the departments that meet the aggregate condition.

SQL HAVING syntax

The usual pattern is:

SELECT grouped_column, aggregate_function(column_name)
FROM table_name
WHERE row_condition
GROUP BY grouped_column
HAVING aggregate_function(column_name) condition
ORDER BY grouped_column;

The logical flow is often easier to remember like this:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

That is why aggregate filters belong in HAVING, not in WHERE.

HAVING vs WHERE

This comparison is one of the biggest search-intent expectations for the keyword.

Example

SELECT customer_id, COUNT(*) AS total_orders
FROM orders
WHERE order_date >= DATE '2025-01-01'
GROUP BY customer_id
HAVING COUNT(*) >= 3;

Here:

• WHERE removes old rows first
• GROUP BY creates one group per customer
• HAVING keeps only customers with at least three matching orders

If you want a refresher on row-level filtering, see SQL WHERE Clause.

SQL HAVING examples with COUNT, SUM, and AVG

Assume this sample table:

CREATE TABLE Orders (
    order_id INT,
    customer_id INT,
    region VARCHAR(50),
    amount DECIMAL(10, 2),
    status VARCHAR(20)
);

HAVING with COUNT

SELECT region, COUNT(*) AS total_orders
FROM Orders
GROUP BY region
HAVING COUNT(*) > 10;

Use this to find regions with a meaningful number of orders.

HAVING with SUM

SELECT region, SUM(amount) AS total_sales
FROM Orders
GROUP BY region
HAVING SUM(amount) > 5000
ORDER BY total_sales DESC;

This is common in revenue reports.

HAVING with AVG

SELECT customer_id, AVG(amount) AS average_order_value
FROM Orders
GROUP BY customer_id
HAVING AVG(amount) >= 250;

This helps isolate higher-value customers.

If you want more aggregate examples, read SQL COUNT(), AVG(), and SUM() Functions.

HAVING with multiple conditions

Many top-ranking tutorials cover only one aggregate condition, but real queries often need more than one.

SELECT region,
       COUNT(*) AS total_orders,
       SUM(amount) AS total_sales
FROM Orders
GROUP BY region
HAVING COUNT(*) >= 10
   AND SUM(amount) > 5000;

You can also combine conditions with OR:

SELECT region, AVG(amount) AS average_order_value
FROM Orders
GROUP BY region
HAVING AVG(amount) > 300
    OR COUNT(*) > 50;

When a query starts getting complex, use clear aliases and preview the grouped output first. That makes the logic easier to trust.

HAVING with COUNT DISTINCT

You can also use HAVING to filter groups based on unique values instead of raw row counts.

SELECT customer_id,
       COUNT(DISTINCT region) AS active_regions
FROM Orders
GROUP BY customer_id
HAVING COUNT(DISTINCT region) >= 2;

This pattern is useful when duplicate rows would overstate the result. Examples include customers buying across multiple regions, users logging in from several devices, or products sold in more than one category.

HAVING with JOINs

This is a common missing section in thinner tutorials.

Suppose you want only customers with at least three paid orders:

SELECT c.customer_name, COUNT(o.order_id) AS paid_orders
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'Paid'
GROUP BY c.customer_name
HAVING COUNT(o.order_id) >= 3
ORDER BY paid_orders DESC;

This kind of pattern appears in dashboards, analytics exports, customer segmentation, and fraud detection reports.

Can you use HAVING without GROUP BY?

Sometimes, yes. When there is no GROUP BY, the database treats the full result as one group.

SELECT COUNT(*) AS total_orders
FROM orders
HAVING COUNT(*) > 100;

This is valid in several database systems, but it is not the most common use of HAVING. In day-to-day SQL, you will usually pair it with GROUP BY.

Aliases, portability, and database tips

Some databases let you reference a SELECT alias directly in HAVING, while others are stricter:

SELECT region, SUM(amount) AS total_sales
FROM Orders
GROUP BY region
HAVING SUM(amount) > 5000;

Using the full aggregate expression is the safest cross-database choice. It also keeps the intent clear when you move between MySQL, PostgreSQL, SQL Server, and Oracle.

Common mistakes and optimization tips

Common mistakes

1. Using WHERE with aggregate functions

-- Wrong
SELECT region, COUNT(*)
FROM Orders
WHERE COUNT(*) > 5
GROUP BY region;

2. Forgetting to group non-aggregated columns

Every selected non-aggregate column must also be grouped appropriately.

3. Using HAVING when WHERE would be better

If you can filter rows earlier, do it in WHERE. That usually makes the grouped query cheaper.

Practical optimization tip

Push row-level conditions into WHERE whenever possible:

SELECT region, SUM(amount) AS total_sales
FROM Orders
WHERE status = 'Paid'
GROUP BY region
HAVING SUM(amount) > 5000;

That pattern is usually better than grouping all rows and filtering too late.

Use HAVING in DbSchema

DbSchema is a practical place to learn and test HAVING queries because grouped filters are easier to reason about when you can inspect the schema and data at the same time.

You can:

1. connect through a driver such as the MySQL JDBC driver or PostgreSQL JDBC driver
2. run grouped queries in the SQL Editor
3. build the base query visually in the Query Builder
4. inspect source tables before and after filtering so you understand exactly which rows become groups
5. compare the grouped result to the live schema in the diagram documentation when you need to explain the query to a teammate

That workflow is helpful when you are teaching SQL, debugging a report, or exploring data interactively before exporting the final query to your application code.

If your result also needs sorting after aggregation, review SQL ORDER BY and SQL GROUP BY Explained.

FAQ

What is the difference between HAVING and WHERE?

WHERE filters rows before grouping. HAVING filters groups after aggregation.

Can I use HAVING without GROUP BY?

In several database systems, yes. The whole result is treated as one group.

Can I use multiple conditions in HAVING?

Yes. You can combine aggregate expressions with AND and OR.

Can I use HAVING with COUNT DISTINCT?

Yes. HAVING COUNT(DISTINCT column) > n is a common way to filter groups based on unique values instead of raw row totals.

Does HAVING work with JOINs?

Yes. It is common to join tables first, group the result, and then filter with HAVING.

Should I use column aliases inside HAVING?

That depends on the database. Some engines allow it, others are stricter. Using the full aggregate expression is often the safest portable choice.

Conclusion

The SQL HAVING clause is the right tool whenever you need to filter grouped results instead of individual rows. Once you understand how it fits after GROUP BY, it becomes much easier to write reporting and analytics queries with confidence.

Use DbSchema to practice the query flow visually, test grouped filters against real data, and move between query building, table inspection, and documentation without leaving the same workspace.