Tutorial on SQL HAVING Command

 
In this tutorial we are going to learn, how to limit the output depending on the corresponding sum (or any other aggregate functions). For instance, we might want to view only the stores having sales over $1,500. Rather than using the WHERE clause in the SQL statement.
 
We might need to use the HAVING clause, that is reserved for aggregate functions.
 
The HAVING clause is generally placed at the end of the SQL statement, and a SQL statement equipped with the HAVING clause may or may not consists of the GROUP BY clause.
 
The standard syntax for HAVING is given below:
 
SELECT [“column_name1”], Function(“column_name2”)
FROM “table_name”
[GROUP BY “column_name1”]
HAVING (arithmetic function condition);
 
Please Note: We are allowed to select zero, one, or more columns in addition to the aggregate function. In case we select zero columns, there is no requirement for the GROUP BY clause.
 
In the given example, table name StoreInformation:

StoreName Sales TxnDate
Los Angeles 1500 Jan-05-1999
San Diego 250 Jan-07-1999
Los Angeles 300 Jan-08-1999
Boston 700 Jan-08-1999

 
We may give following query:
 
SELECT StoreName, SUM(Sales)
FROM StoreInformation
GROUP BY StoreName
HAVING SUM(Sales) > 1500;
 
Outcome:

StoreName SUM(Sales)
LosAngeles 1800

 

Practice Exercises

To perform these exercises, assume that you have a table named RegionSales with the following given data:
 
Table Name RegionSales

Region Year Orders TotalSales
West 2013 1560 325000
West 2014 1820 380000
North 2013 790 148000
North 2014 995 185000
East 2013 1760 375000
East 2014 2220 450000
South 2013 1790 388000
South 2014 1695 360000

 
1. Find which of the following given order is correct for a SQL statement?
 
a) SELECT…FROM…ORDER BY…WHERE…HAVING
b) SELECT…FROM…WHERE…ORDER BY…HAVING
c) SELECT…WHERE…FROM…HAVING…ORDER BY
d) SELECT…FROM…WHERE…HAVING…ORDER BY
 
2. What is the outcome of the following SQL statement?
 
SELECT Region, SUM(Orders) FROM Region_Sales GROUP BY Region HAVING SUM(Orders) > 2500;
 
3. What is the outcome of the following given SQL statement?
 
SELECT Region, SUM(Orders) FROM Region_Sales WHERE Total_Sales < 385000 GROUP BY Region HAVING SUM(Orders) > 2500;
 

Answers

1. d)
 
2. The outcome is:

Region SUM(Orders)
East 3960
South 3485
West 3380

 
3. The outcome is:

Region SUM(Orders)
West 3380

 
In this given SQL statement, we are going to first apply the WHERE clause “TotalSales < 385000”. That truncate the two rows where Total Sales is larger than 385000.
 
We are then going to sum up the orders from the remaining left rows by region, and this leads to the following outcome:

Region SUM(Orders)
West 3380
North 1785
East 1760
South 1695

 
The only region which satisfies the HAVING clause, “SUM(Orders) > 2500”, is West.

Kristin is a content strategist at Techarex Networks. Kristin follows the B2B technology space closely and loves to write on the latest changes in technology, futuretech and fixes for day to day how to issues. Besides writing Kristin also loves music, moves and skating.