SQL > SQL Commands >
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.
The syntax for HAVING is,
SELECT ["column_name1"], "function name" ("column_name2")
[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.
We use the following table for our example.
To see only the stores with sales over $1,500, we would type,
SELECT Store_Name, SUM(Sales)
GROUP BY Store_Name
HAVING SUM(Sales) > 1500;
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.
For these exercises, assume we have a table called Region_Sales with the following data:
1. Which of the following order is correct for a SQL statement?
2. What is the result of the following SQL statement?
3. What is the result of the following SQL statement?