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 name" ("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_Name  Sales  Txn_Date 
 Los Angeles  1500  Jan-05-1999 
 San Diego  250  Jan-07-1999 
 Los Angeles  300  Jan-08-1999 
 Boston  700  Jan-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_Name SUM(Sales)
Los Angeles 1800

The first three lines of the code yield three rows: Los Angeles with Sale of 1,800, San Diego with Sales of 250, and Boston with Sales of 700. The HAVING clause then acts on these three rows. Total sales for both San Diego and Boston are below $1,500, so the "HAVING SUM(Sales) > 1500" clause filters out these two stores, leaving Los Angeles as the only row returned for this SQL.

Exercises

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

Table Region_Sales

 Region  Year  Orders  Total_Sales 
 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. 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

This page was last updated on June 19, 2023.




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