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_id
customer_name
customer_city
items_ordered
order_value
1
Alicia Keys
Atlanta
5
543.05
6
Lauren Crow
Seattle
10
999.99
7
Stephen Fleming
Seattle
1
106.49
12
Rameses Williams
New York
3
199.98
13
Stacey John
Atlanta
3
207.7
14
John Williams
Washington
10
1001.01
15
Kevin Spacey
New York
11
1002.5
16
Linda Blair
New York
5
666.66
17
Laura Hopkins
Washington
3
223.89
18
Anthony Gonsalves
Washington
3
200.05
19
Julie Carrie
Seattle
20
1195.3
20
Mike O’Brien
Seattle
12
887.88
21
Niki Dawson
Seattle
5
673
22
Jamie Collins
Washington
1
95.25
23
Jeremy Corbin
New York
10
972.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_city
largest_oder
New York
11
Seattle
20
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_city
order_count
Atlanta
2
New York
4
Washington
4
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_city
total_ value
Atlanta
750.75
Washington
1520.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.