Filtering Data with SQL HAVING
The HAVING clause in SQL is used to filter data after it has been grouped with the GROUP BY clause. While the WHERE clause filters rows before the grouping process, HAVING filters groups that result from the GROUP BY operation. It is particularly useful when you need to apply conditions to aggregate functions like SUM(), COUNT(), AVG(), MAX(), and MIN().
This chapter will provide a comprehensive understanding of the HAVING clause, explaining its uses from basic to advanced, along with examples, code explanations, and outputs. By the end, you’ll have a complete understanding of how to use HAVING in SQL.
What is SQL HAVING?
The HAVING clause allows you to filter groups of data after the grouping has been performed with the GROUP BY clause. It is used to set conditions on aggregate functions, which the WHERE clause cannot handle.
Syntax
SELECT column_name(s), aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
- column_name(s): Columns you want to retrieve.
- aggregate_function: Functions like
SUM(),COUNT(),AVG(),MAX(), etc. - table_name: The table from which data is being queried.
- WHERE: Optional. Filters rows before grouping.
- GROUP BY: Groups rows based on specified columns.
- HAVING: Filters the grouped data based on conditions.
- ORDER BY: Optional. Sorts the result set.
Difference Between WHERE and HAVING
The primary difference between WHERE and HAVING is when the filtering takes place:
- WHERE: Filters rows before the
GROUP BYclause. - HAVING: Filters groups after the
GROUP BYclause.
WHERE cannot be used with aggregate functions like SUM(), COUNT(), AVG(), etc., while HAVING can.
Example: Basic Use of HAVING
Consider a Sales table:
| SaleID | ProductID | Quantity | Price | SalesPerson | SaleDate |
|---|---|---|---|---|---|
| 1 | 101 | 5 | 10 | John | 2023-01-10 |
| 2 | 102 | 2 | 20 | Sarah | 2023-01-11 |
| 3 | 101 | 4 | 10 | John | 2023-01-12 |
| 4 | 103 | 3 | 15 | John | 2023-01-12 |
| 5 | 102 | 6 | 20 | Sarah | 2023-01-13 |
Task: Find salespersons who sold more than 5 units in total.
SELECT SalesPerson, SUM(Quantity) AS TotalQuantity
FROM Sales
GROUP BY SalesPerson
HAVING SUM(Quantity) > 5;
Output:
| SalesPerson | TotalQuantity |
|---|---|
| John | 12 |
| Sarah | 8 |
Explanation:
- The query first groups rows by
SalesPerson. SUM(Quantity)calculates the total quantity sold for each salesperson.- The
HAVINGclause filters the result set to only include those salespeople who have sold more than 5 units.
Using HAVING with Multiple Conditions
You can apply multiple conditions in the HAVING clause using logical operators such as AND and OR. For example, let’s extend the previous scenario and filter salespeople who sold more than 5 units and made a total sales amount greater than 100.
SELECT SalesPerson, SUM(Quantity) AS TotalQuantity, SUM(Quantity * Price) AS TotalSales
FROM Sales
GROUP BY SalesPerson
HAVING SUM(Quantity) > 5 AND SUM(Quantity * Price) > 100;
Output:
| SalesPerson | TotalQuantity | TotalSales |
|---|---|---|
| Sarah | 8 | 160 |
Explanation:
- The query groups data by
SalesPerson. SUM(Quantity * Price)calculates the total sales for each salesperson.- The
HAVINGclause ensures that only salespeople who sold more than 5 units and made total sales greater than 100 are returned.
Example: Using HAVING with COUNT()
Suppose you have a Customers table:
| CustomerID | Name | City |
|---|---|---|
| 1 | Alice | New York |
| 2 | Bob | Los Angeles |
| 3 | Charlie | New York |
| 4 | David | New York |
| 5 | Eve | Los Angeles |
Task: Find cities that have more than 2 customers.
SELECT City, COUNT(*) AS CustomerCount
FROM Customers
GROUP BY City
HAVING COUNT(*) > 2;
Output:
| City | CustomerCount |
|---|---|
| New York | 3 |
Explanation:
- The query groups rows by
City. COUNT(*)calculates the number of customers in each city.- The
HAVINGclause filters to include only cities with more than 2 customers.
Filtering with Aggregate Functions Using HAVING
Task: Find salespeople who have an average sale price greater than 15.
SELECT SalesPerson, AVG(Price) AS AveragePrice
FROM Sales
GROUP BY SalesPerson
HAVING AVG(Price) > 15;
Output:
| SalesPerson | AveragePrice |
|---|---|
| Sarah | 20 |
Explanation:
- The query groups rows by
SalesPerson. AVG(Price)calculates the average price of sales for each salesperson.- The
HAVINGclause filters to include only salespeople with an average sale price greater than 15.
Combining HAVING with ORDER BY
You can use ORDER BY along with HAVING to sort the filtered results. For instance, you might want to list salespeople by total sales in descending order, but only include those who sold more than 10 units.
SELECT SalesPerson, SUM(Quantity) AS TotalQuantity
FROM Sales
GROUP BY SalesPerson
HAVING SUM(Quantity) > 10
ORDER BY TotalQuantity DESC;
Output:
| SalesPerson | TotalQuantity |
|---|---|
| John | 12 |
Explanation:
- The query filters salespeople with
SUM(Quantity) > 10usingHAVING. - The
ORDER BYclause sorts the result byTotalQuantityin descending order.
Advanced Example: HAVING with Date Functions
Consider a scenario where you have a Sales table with SaleDate, and you want to find salespeople who made more than 5 sales in January 2023.
SELECT SalesPerson, COUNT(SaleID) AS SalesCount
FROM Sales
WHERE SaleDate BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY SalesPerson
HAVING COUNT(SaleID) > 5;
Explanation:
- The
WHEREclause filters rows for sales made in January 2023. - The query groups rows by
SalesPersonand counts the number of sales. - The
HAVINGclause filters to include only salespeople with more than 5 sales.
The HAVING clause is a powerful tool in SQL for filtering grouped data after aggregation. While the WHERE clause is useful for filtering individual rows before grouping, HAVING allows you to filter on aggregate results. Whether you're working with SUM(), COUNT(), AVG(), or other aggregate functions, HAVING can help you refine your results to meet specific conditions. Happy coding !❤️