{"id":219,"date":"2018-04-23T21:55:48","date_gmt":"2018-04-23T14:55:48","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=219"},"modified":"2024-08-16T19:14:31","modified_gmt":"2024-08-16T12:14:31","slug":"sql-server-having","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-having\/","title":{"rendered":"SQL Server HAVING Clause"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server HAVING<\/code> clause to filter the groups based on specified conditions.<\/p>\n\n\n\n

Introduction to SQL Server HAVING clause #<\/a><\/h2>\n\n\n\n

The HAVING<\/code> clause is often used with the GROUP BY<\/a><\/code> clause to filter groups based on a specified list of conditions. The following illustrates the HAVING<\/code> clause syntax:<\/p>\n\n\n

SELECT<\/span>\n    select_list\nFROM<\/span>\n    table_name\nGROUP<\/span> BY<\/span>\n    group_list\nHAVING<\/span>\n    conditions;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Because SQL Server processes the HAVING<\/code> clause after the GROUP BY<\/code> clause, you cannot refer to the aggregate function<\/a> specified in the select list by using the column alias<\/a>. The following query will fail:<\/p>\n\n\n
SELECT<\/span>\n    column_name1,\n    column_name2,\n    aggregate_function (column_name3) column_alias\nFROM<\/span>\n    table_name\nGROUP<\/span> BY<\/span>\n    column_name1,\n    column_name2\nHAVING<\/span>\n    column_alias > value<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Instead, you need to use the aggregate function expression in the HAVING<\/code> clause explicitly as follows:<\/p>\n\n\n
SELECT<\/span>\n    column_name1,\n    column_name2,\n    aggregate_function (column_name3) alias<\/span>\nFROM<\/span>\n    table_name\nGROUP<\/span> BY<\/span>\n    column_name1,\n    column_name2\nHAVING<\/span>\n    aggregate_function (column_name3) > value<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL Server HAVING examples #<\/a><\/h2>\n\n\n\n
Let’s take some examples to understand how the HAVING<\/code> clause works.<\/p>\n\n\n\n
SQL Server HAVING with the COUNT function example #<\/a><\/h3>\n\n\n\n
See the following orders<\/code> table from the sample database<\/a>:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The following statement uses the HAVING<\/code> clause to find the customers who placed at least two orders per year:<\/p>\n\n\n
SELECT<\/span>\n    customer_id,\n    YEAR<\/span> (order_date),\n    COUNT<\/span> (order_id) order_count\nFROM<\/span>\n    sales.orders\nGROUP<\/span> BY<\/span>\n    customer_id,\n    YEAR<\/span> (order_date)\nHAVING<\/span>\n    COUNT<\/span> (order_id) >= 2<\/span>\nORDER<\/span> BY<\/span>\n    customer_id;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"\"<\/figure>\n\n\n\n
In this example:<\/p>\n\n\n\n
In this syntax, the GROUP BY<\/a><\/code> clause summarizes the rows into groups and the HAVING<\/code> clause applies one or more conditions to these groups. Only groups that make the conditions evaluated TRUE<\/code> are included in the result. In other words, the groups for which the condition evaluates to\u00a0 FALSE<\/code> or UNKNOWN<\/code> are filtered out.<\/p>\n\n\n\n