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

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

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

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