SQL Server HAVING Clause

Summary: 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;
Code language: SQL (Structured Query Language) (sql)

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 evaluated 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;
Code language: SQL (Structured Query Language) (sql)

Instead, you need to 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;
Code language: SQL (Structured Query Language) (sql)

SQL Server HAVING examples

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

SQL Server HAVING with the COUNT function example

See the following orders table from the sample database:

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;
Code language: SQL (Structured Query Language) (sql)

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 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

Consider the following order_items table:

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;
Code language: SQL (Structured Query Language) (sql)

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

See the following products table:

The following statement first finds the maximum and minimum list prices in each product category. Then, it filters out the category which has a maximum list price greater than 4,000 or a 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;
Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Summary

• Use the SQL Server HAVING clause to filter groups based on specified conditions.