SQL Server: HAVING Clause

In this post explains how to use the HAVING clause in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) HAVING clause is used in combination with the GROUP BY clause to restrict the groups of returned rows to only those whose the condition is TRUE.

Syntax

The syntax for the HAVING clause in SQL Server (Transact-SQL) is:

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n
HAVING having_condition;

Parameters or Arguments

aggregate_function

It can be a function such as SUM, COUNT, MIN, MAX, or AVG functions.

expression1, expression2, ... expression_n

The expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY clause.

WHERE conditions

Optional. These are the conditions for the records to be selected.

HAVING having_condition

This is a further condition applied only to the aggregated results to restrict the groups of returned rows. Only those groups whose condition evaluates to TRUE will be included in the result set.

Example - Using SUM function

Let's look at a SQL Server HAVING clause example that uses the SUM function.

For example:

SELECT department, SUM(quantity) AS "Total Quantity"
FROM products
GROUP BY department
HAVING SUM(quantity) > 100;

This HAVING clause example uses the SUM function to return the name of the department and the total quantity (in the associated department). The SQL Server HAVING clause will filter the results so that only departments with total quantity greater than 100 will be returned.

Example - Using COUNT function

Let's look at how we could use the HAVING clause with the COUNT function.

For example:

SELECT city, COUNT(*) AS "Number of employees"
FROM employees
WHERE state = 'California'
GROUP BY city
HAVING COUNT(*) > 20;

This HAVING clause example uses the COUNT function to return the city and the number of employees (residing in that city) that are in the state of 'California'. The SQL Server HAVING clause will filter the results so that only cities in California with more than 20 employees will be returned.

Example - Using MIN function

Let's next look at how we could use the HAVING clause with the MIN function.

For example:

SELECT department, MIN(salary) AS "Lowest salary"
FROM employees
GROUP BY department
HAVING MIN(salary) >= 50000;

This HAVING example uses the MIN function to return the name of each department and the minimum salary in the department. The SQL Server HAVING clause will return only those departments where the minimum salary is greater than or equal to $50,000.

Example - Using MAX function

For example:

SELECT last_name, MAX(salary) AS "Highest salary"
FROM employees
GROUP BY last_name
HAVING MAX(salary) > 34000;

This HAVING clause example uses the MAX function to return the last_name of the employee and the maximum salary for that last_name value. The SQL Server HAVING clause will return only those last_name values whose maximum salary is greater than $34,000.