SQL Server HAVING

Learning Objective

The objective of this SQL Server tutorial is to teach you how to use the HAVING clause with aggregate function and GROUP BY in a SQL query.

What is an Aggregate Function?

An Aggregate Function includes SUM, COUNT, MIN, MAX, or AVG functions. They are so called because they operate on the values in a column or resultset and return a single aggregate value which is either the sum of all the values, the count of the total number of values etc. depending upon the function used.

What is HAVING in SQL Server?

The HAVING clause in SQL Server is used to evaluate the result or value returned by an aggregate function against the condition specified in the HAVING clause. The different conditions that can be specified with the HAVING clause are <, >, <=, >=,! = and <>. != and <> both mean not equal to. HAVING is similar to and an alternative to the WHERE clause since WHERE cannot evaluate aggregate functions but only the values in a column or expression.

Operation

The HAVING clause mandatorily operates with the GROUP BY clause and follows the GROUP BY clause in a statement. It can be optionally followed by ORDER BY which is used to format the output. It evaluates the aggregate function condition on the groups generated by GROUP BY and retain only those records in the final output which conform to the aggregate function condition. Hence it generates a condensed output containing only those columns on which the GROUP BY and HAVING clause operates and allows us to deep dive and do a finer analysis of groups their properties.

SQL Server HAVING Syntax

The basic syntax of the SQL Server HAVING clause is as follows.

SELECT expressions
,aggregate function (expression) alias
FROM tables
[WHERE condition]
[GROUP BY expressions]
[HAVING aggregate function (expression) condition]

In this syntax,

  • expressions – expressions defined here are the column(s) or calculation you want to retrieve. If you want to retrieve all the columns simply use * in the place of expressions.
  • aggregate function – Please refer definition above. An aggregate function can be applied to a single  column or even to multiple columns combined by a mathematical operation (e.g. like the average of the product of 2 columns).
  • alias – a name or heading for the values generated by the aggregate function. Otherwise the values will show without column heading in the resultset.
  • tables – one or more than one table from where you want to retrieve data.
  • WHERE conditions –   Optional. This is used to specify some conditions while selecting data. In case you are not using WHERE clause all the rows available will be selected.
  • GROUP BY – clause used to aggregate records from the table or an intermediary resultset (like the filtered resultset generated by WHERE clause if used before) where the value for a particular column is the same for all the records.
  • HAVING condition– clause used to specify an aggregate function whose result can be tested on the groups generated by GROUP BY.

SQL Server HAVING Examples

  • Columns are also referred to as fields or attributes and the terms are used interchangeably.
  • A row of information in a table is called a tuple.

Let us see how the HAVING clause works with the different aggregate functions. Suppose we have a table called customers containing the below data about customer orders. We will query the same to understand the different usage scenarios.

customer_idcustomer_namecustomer_cityitems_orderedorder_value
1Alicia KeysAtlanta5543.05
6Lauren CrowSeattle10999.99
7Stephen FlemingSeattle1106.49
12Rameses WilliamsNew York3199.98
13Stacey JohnAtlanta3207.7
14John WilliamsWashington101001.01
15Kevin SpaceyNew York111002.5
16Linda BlairNew York5666.66
17Laura HopkinsWashington3223.89
18Anthony GonsalvesWashington3200.05
19Julie CarrieSeattle201195.3
20Mike O’BrienSeattle12887.88
21Niki DawsonSeattle5673
22Jamie CollinsWashington195.25
23Jeremy CorbinNew York10972.82
Table Name:  Customers

1)  SQL Server HAVING – MAX, MIN example

Suppose we want to find out if the maximum number of items ordered (i.e. order size) from any city exceeds 10. We can do so using the below query. The below query groups the resultset city wise using GROUP BY, finds the largest order size for each city using the MAX function on the items_ordered column, checks whether it is greater than 10 using HAVING and outputs only those rows where the condition is satisfied.

SELECT 
customer_city
,max(items_ordered) largest_oder
FROM customers
GROUP BY customer_city
HAVING max(items_ordered) > 10;

The query will generate the below output which shows that customers from the cities of New York and Seattle have placed orders where the order size was more than 10.

customer_citylargest_oder
New York11
Seattle20

2)  SQL Server HAVING – COUNT example

The below is a HAVING example with the COUNT function. The query groups the resultset city wise using GROUP BY, determines the count of orders received from a city (i.e. total number of orders) using the COUNT function on the items_ordered column, checks that the count is not equal to 5 using HAVING and outputs only those rows which satisfies the condition.

SELECT 
customer_city
,count(items_ordered) order_count
FROM customers
GROUP BY customer_city HAVING count(items_ordered) <> 5;

The query generates the following output which shows that the number of orders received from all customers for the cities of Atlanta, New York and Washington is not equal to 5.

customer_cityorder_count
Atlanta2
New York4
Washington4

3)  SQL Server HAVING – SUM example

The below is a HAVING example with SUM function. The query groups the resultset city wise using GROUP BY, determines the total value of orders for a city using the SUM function on the order_value column, checks that the total is less than 2000 using HAVING and outputs only those rows which satisfies the condition.

SELECT 
customer_city
,sum(order_value) total_value
FROM customers
GROUP BY customer_city
HAVING sum(order_value) < 2000;

The query generates the following output which shows that the total order value from all customers for Atlanta and Washington is less than 2000.

customer_citytotal_ value
Atlanta750.75
Washington1520.2

4)  SQL Server HAVING – AVG example

The below is a HAVING example with AVG function. The query groups the resultset city wise using GROUP BY, determines the average value of orders for a city using the AVG function on the order_value column, checks that the value is between 500 and 1000 using HAVING and outputs only those rows which satisfies the condition.

SELECT 
customer_city
,avg(order_value) order_average
FROM customers
GROUP BY customer_city
HAVING avg(order_value) BETWEEN 500 and 1000;

The query generates the following output which shows that the average value of orders placed from the cities of New York and Seattle lies between the range specified.

customer_cityorder_average
New York710.49
Seattle772.532

Advertisement