Introduction to SQL Server Having

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:
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:
Instead, you must use the aggregate function expression in the HAVING clause explicitly as follows:

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

SQL Server HAVING clause with AVG() function example

The following statement finds product categories whose average list prices are between 500 and 1,000:
In this tutorial, you have learned how to use the SQL Server HAVING clause to filter groups based on specified conditions.

Post a Comment