SQL HAVING Clause - Scaler Topics

The HAVING clause in SQL is a powerful tool for filtering data. It's like the WHERE clause, but for groups. Imagine you've already grouped your data using the GROUP BY clause. Now, you want to apply a filter to these groups. That's where HAVING steps in, allowing you to specify conditions that these groups must meet. It's incredibly useful for analyzing data, helping you narrow down to the exact insights you're looking for.

Syntax

SELECT column1, aggregate_function(column2): This part of the query specifies what you want to select. column1 is the column you're grouping by, and aggregate_function(column2) applies an aggregate function (like SUM, AVG, MAX, MIN, COUNT) to column2 for each group.
FROM table_name: Specifies the table from which you're retrieving the data.
GROUP BY column1: Groups the rows that have the same values in column1 into summary rows.
HAVING condition: Finally, the HAVING clause filters these groups based on a specified condition. Unlike the WHERE clause, which filters rows, HAVING filters groups.

The beauty of the HAVING clause in SQL lies in its ability to perform a filter operation on data aggregates, making it indispensable for complex data analysis tasks.

Demo Database

Let's create a database for a fictional bookstore. The table will hold records of sales, including the category of books sold, the date of sale, and the total sales amount for each transaction. This example will allow us to demonstrate the HAVING clause effectively.

SQL Code for Creating the Table

SaleID	Category	SaleDate	Amount
1	Fiction	2023-03-01	120.00
2	Science Fiction	2023-03-05	200.00
3	Fiction	2023-03-12	150.00
4	Non-Fiction	2023-03-15	180.00
5	Science Fiction	2023-03-22	220.00

HAVING Clause in SQL Examples

Example 1: Filter Categories with Total Sales Over a Certain Amount

This query aims to find the book categories that have accumulated more than $300 in sales.

Stepwise Explanation:

SELECT Category, SUM(Amount) AS TotalSales: This line selects the book categories and calculates the total sales amount for each category.
FROM BookSales: Specifies that the data comes from the BookSales table.
GROUP BY Category: Groups the results by the book category.
HAVING SUM(Amount) > 300: Filters out the categories where the total sales are not greater than $300.

Output:

Category	TotalSales
Science Fiction	420.00

This output shows that only the "Science Fiction" category has total sales exceeding $300.

Example 2: Filter Categories with More Than One Sale

This query is designed to identify the categories that have more than one sale recorded.

Stepwise Explanation:

SELECT Category, COUNT(SaleID) AS NumberOfSales: Selects the book categories along with a count of how many sales occurred in each category.
FROM BookSales: Indicates that the data is sourced from the BookSales table.
GROUP BY Category: The results are grouped by the category of the books.
HAVING COUNT(SaleID) > 1: This line filters the results to show only those categories where there are more than one sale.

Output:

Category	NumberOfSales
Fiction	2
Science Fiction	2

The output demonstrates that both "Fiction" and "Science Fiction" categories had more than one sale, fulfilling the condition specified in the HAVING clause.

Difference between HAVING and WHERE CLAUSE

Both HAVING and WHERE clauses are used to filter the data in SQL queries but they still differ in many aspects which are mentioned below:

HAVING	WHERE
The HAVING clause is used to filter data from groups according to the specified condition.	The WHERE clause is used to filter individual content from table according to the specified condition.
HAVING clause is used after the groups are made (Post-filter).	WHERE clause is used before the groups are made (Pre-filter).
HAVING clause needs to be executed with the GROUP BY clause.	WHERE clause can be executed without the GROUP BY clause.
In SQL queries, the HAVING clause is used after the GROUP BY clause.	In SQL queries, the WHERE clause is used before the GROUP BY clause.
The HAVING clause is exclusively utilized alongside the SELECT statement to filter data.	WHERE clause can be used with SELECT, UPLOAD and DELETE statements.
SQL aggregate functions can be used with the HAVING clause in a query.	SQL aggregate functions can not be used with the WHERE clause in a query.
HAVING clause is used in column operations.	WHERE clause is used in row operations.

Conclusion

The HAVING clause in SQL is used to filter results obtained by the GROUP BY clause based on some specific conditions.
The HAVING clause in SQL is similar to the WHERE clause but the HAVING clause is used to filter groups while the WHERE clause is used to filter individual content from the table.
In a query, the HAVING clause is positioned following the GROUP BY clause and preceding the ORDER BY clause.
The HAVING clause in SQL can only be used with the SELECT clause.