SQL Having

In SQL, the HAVING clause is used to filter grouped data and aggregate results. While the WHERE clause filters individual rows before grouping occurs, the HAVING clause filters the summary rows after the GROUP BY calculation is performed.

Because aggregate functions like SUM(), COUNT(), AVG(), MAX(), and MIN() cannot be used inside a WHERE clause, HAVING is the required mechanism to filter records based on aggregated metrics.

It acts exactly like a WHERE clause, but with one major difference: WHERE filters individual rows before they are grouped, while HAVING filters the group summaries after the calculation takes place

Basic Syntax Blueprint

The HAVING clause must always be placed directly after GROUP BY and right before ORDER BY.

SELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
WHERE row_condition              -- 1. Filters raw data first
GROUP BY column_name            -- 2. Groups the remaining rows
HAVING aggregate_condition       -- 3. Filters the calculated groups
ORDER BY column_name;            -- 4. Sorts the final output

Complete Query Order

To use HAVING correctly, it must follow a strict order of keywords within your SQL statement:

SELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
WHERE row_condition
GROUP BY column_name
HAVING aggregate_condition
ORDER BY column_name;

Direct Comparison: WHERE vs. HAVING

Code Examples

Basic HAVING Query

If you need to find which store departments employ more than 5 people, you use HAVING to evaluate the count:

SELECT department, COUNT(employee_id) AS total_employees
FROM company_workforce
GROUP BY department
HAVING COUNT(employee_id) > 5;

Combining WHERE and HAVING Together

You can narrow down your dataset row-by-row first, then evaluate the aggregated totals. This query calculates total sales for online orders only, and then isolates products that brought in over $10,000:

SELECT product_id, SUM(sales_amount) AS total_revenue
FROM sales_records
WHERE order_type = 'Online' -- 1. Filters rows first
GROUP BY product_id
HAVING SUM(sales_amount) > 10000; -- 2. Filters aggregated totals second

Multiple Aggregation Filters

Just like a WHERE clause, you can combine multiple logical conditions in a HAVING statement using AND or OR. This isolates large departments with exceptionally high average payouts:

SELECT department, COUNT(*) AS staff_count, AVG(salary) AS average_pay
FROM company_workforce
GROUP BY department
HAVING COUNT(*) > 10 AND AVG(salary) >= 85000;

A Common Alias Shortcut

In some modern database management systems (like MySQL and PostgreSQL), you can use the alias you defined in the SELECT clause directly inside your HAVING clause to keep code neat.

-- Works seamlessly in MySQL and PostgreSQL
SELECT department, SUM(salary) AS total_payroll
FROM employees
GROUP BY department
HAVING total_payroll > 500000;

Note: Oracle and SQL Server do not always support this, so writing the full aggregate function out inside HAVING is the safest cross-platform practice.

More Examples

Filtering Groups via COUNT()

Imagine you want to look for potential fraud or high-volume buyers. This query pulls only the customers who have placed more than 5 orders total.

SELECT customer_id, COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 5;

Filtering Groups via AVG() and Combining with WHERE

This query filters the raw data first to look only at "Active" employees, groups them by department, and then uses HAVING to isolate departments where the average salary is above ₹80,000.

SELECT department, AVG(salary) AS average_salary
FROM employees
WHERE status = 'Active'               -- Filter individual rows first
GROUP BY department
HAVING AVG(salary) > 80000            -- Filter grouped results last
ORDER BY average_salary DESC;

Filtering Groups via SUM()

To find store locations that are generating high-tier revenue:

SELECT store_id, SUM(sale_amount) AS total_revenue
FROM sales
GROUP BY store_id
HAVING SUM(sale_amount) >= 500000;