HAVING clause in SQL Server

In this tutorial, you will learn how to use the SQL Server HAVING clause to filter the groups based on specified conditions.

Introduction to SQL Server HAVING clause

The HAVING clause is often used with the GROUP BY clause to filter groups based on a specified list of conditions. The following illustrates the HAVING clause syntax:

SELECT
    select_list
FROM
    table_name
GROUP BY
    group_list
HAVING
    conditions;

In this syntax, the GROUP BY clause summarizes the rows into groups and the HAVING clause applies one or more conditions to these groups. Only groups that make the conditions evaluate to TRUE are included in the result. In other words, the groups for which the condition evaluates to  FALSE or UNKNOWN are filtered out.

Because SQL Server processes the HAVING clause after the GROUP BY clause, you cannot refer to the aggregate function specified in the select list by using the column alias. The following query will fail:

SELECT
    column_name1,
    column_name2,
    aggregate_function (column_name3) column_alias
FROM
    table_name
GROUP BY
    column_name1,
    column_name2
HAVING
    column_alias > value;

Instead, you must use the aggregate function expression in the HAVING clause explicitly as follows:

SELECT
    column_name1,
    column_name2,
    aggregate_function (column_name3) alias
FROM
    table_name
GROUP BY
    column_name1,
    column_name2
HAVING
    aggregate_function (column_name3) > value;

SQL Server HAVING examples

Let’s take some examples to understand how the HAVING clause works.

SQL Server HAVING with the COUNT function example

The following statement uses the HAVING clause to find the customers who placed at least two orders per year:

SELECT
    customer_id,
    YEAR (order_date),
    COUNT (order_id) order_count
FROM
    sales.orders
GROUP BY
    customer_id,
    YEAR (order_date)
HAVING
    COUNT (order_id) >= 2
ORDER BY
    customer_id;

In this example:

• First, the GROUP BY clause groups the sales order by customer and order year. The COUNT() function returns the number of orders each customer placed in each year.
• Second, the HAVING clause filtered out all the customers whose number of orders is less than two.

SQL Server HAVING clause with the SUM() function example

The following statement finds the sales orders whose net values are greater than 20,000:

SELECT
    order_id,
    SUM (
        quantity * list_price * (1 - discount)
    ) net_value
FROM
    sales.order_items
GROUP BY
    order_id
HAVING
    SUM (
        quantity * list_price * (1 - discount)
    ) > 20000
ORDER BY
    net_value;

In this example:

• First, the SUM() function returns the net values of sales orders.
• Second, the HAVING clause filters the sales orders whose net values are less than or equal to 20,000.

SQL Server HAVING clause with MAX and MIN functions example

The following statement first finds the maximum and minimum list prices in each product category. Then, it filters out the category which has the maximum list price greater than 4,000 or the minimum list price less than 500:

SELECT
    category_id,
    MAX (list_price) max_list_price,
    MIN (list_price) min_list_price
FROM
    production.products
GROUP BY
    category_id
HAVING
    MAX (list_price) > 4000 OR MIN (list_price) < 500;

SQL Server HAVING clause with AVG() function example

The following statement finds product categories whose average list prices are between 500 and 1,000:

SELECT
    category_id,
    AVG (list_price) avg_list_price
FROM
    production.products
GROUP BY
    category_id
HAVING
    AVG (list_price) BETWEEN 500 AND 1000;