SQL > SQL Commands > Having

The HAVING clause is used to filter the result set based on the result of an aggregate function. It is typically located near or at the end of the SQL statement.

HAVING is often coupled with the presence of the GROUP BY clause, although it is possible to have a HAVING clause without the GROUP BY clause.

Syntax

The syntax for HAVING is,

SELECT ["column_name1"], "function type" ("column_name2")
FROM "table_name"
[GROUP BY "column_name1"]
HAVING (arithmetic function condition);

The brackets around "column_name1" and GROUP BY "column_name1" means that they are optional.

Note: We may select zero, one, or more columns in addition to the aggregate function. If we do select any column outside of the aggregate function, there is no need for the GROUP BY clause.

Example

We use the following table for our example.

Table Store_Information

Store_NameSalesTxn_Date
Los Angeles1500Jan-05-1999
San Diego250Jan-07-1999
Los Angeles300Jan-08-1999
Boston700Jan-08-1999

To see only the stores with sales over $1,500, we would type,

SELECT Store_Name, SUM(Sales)
FROM Store_Information
GROUP BY Store_Name
HAVING SUM(Sales) > 1500;

Result:

Store_NameSUM(Sales)
Los Angeles1800

Total sales for both San Diego and Boston are below $1,500, so the "HAVING SUM(Sales) > 1500" clause filters out these two stores.

Exercises

For these exercises, assume we have a table called Region_Sales with the following data:

Table Region_Sales

RegionYearOrdersTotal_Sales
West20131560325000
West20141820380000
North2013790148000
North2014995185000
East20131760375000
East20142220450000
South20131790388000
South20141695360000

1. Which of the following 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 result of the following SQL statement?
SELECT Region, SUM(Orders) FROM Region_Sales GROUP BY Region HAVING SUM(Orders) > 2500;

3. What is the result of the following SQL statement?
SELECT Region, SUM(Orders) FROM Region_Sales WHERE Total_Sales < 385000 GROUP BY Region HAVING SUM(Orders) > 2500;

Next: SQL Alias




Copyright © 2016   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact