PL/SQL HAVING Clause

The PL/SQL HAVING clause is a powerful tool used in SQL for filtering records in groups defined by the GROUP BY clause. While the WHERE clause filters individual rows, the HAVING clause filters groups based on aggregate functions like SUM, COUNT, MIN, and MAX.

This clause is essential when we want to impose conditions on grouped records, making it an indispensable part of SQL queries that involve grouping. In this article, we will learn about the PL/SQL HAVING Clause operator with its syntax and practical applications with examples.

PL/SQL HAVING Clause

• The HAVING clause in PL/SQL (and SQL in general) is used to filter records that work on aggregated data which is similar to how the WHERE clause filters records before aggregation.
  
• The HAVING clause is typically used in conjunction with the GROUP BY clause to specify conditions on groups of rows produced by the aggregation.

Syntax:

SELECT column1, aggregate_function(column2)

FROM table_name

WHERE condition

GROUP BY column1

HAVING aggregate_condition;

Explanation:

• SELECT column1, aggregate_function(column2): This part of the query specifies the columns and aggregate functions that you want to retrieve.
  
• FROM table_name: Indicates the table from which the data is being retrieved.
  
• WHERE condition: This clause is optional and is used to filter records before they are grouped.
  
• GROUP BY column1: This groups the data based on one or more columns.
  
• HAVING aggregate_condition: This clause filters the grouped records based on a condition applied to the aggregate function.

Examples of PL/SQL HAVING Clause

Setting up an Environment

To understand the PL/SQL IS NULL Operator we will use the below table for better understanding.

Products Table:

Explanation:

• The Products table contains product details with a unique ProductID as the primary key, ensuring each product is distinct.

• Each product is associated with a specific Category, organizing them into groups like "Electronics" and "Accessories".
  
• The ProductName column stores the name of each product, making it easy to identify and manage different products.

Sales Table:

Explanation:

• Tracks sales with a reference to the ProductID, linking each sale to a specific product from the Products table.
  
• Includes the QuantitySold to record how many units were sold, and the SaleDate to capture when the sale occurred.
  
• By referencing ProductID, it ensures consistency between the Sales and Products tables, maintaining data integrity.

Example 1: Using SUM Function with HAVING Clause

Let's write a query to determine which products have sold more than 10 units by aggregating sales data from the Products and Sales tables

Query:

SELECT p.ProductName, SUM(s.QuantitySold) AS TotalQuantity
FROM Products p
JOIN Sales s ON p.ProductID = s.ProductID
GROUP BY p.ProductName
HAVING SUM(s.QuantitySold) > 10;

Output:

Explanation:

• The query retrieves the total number of units sold for each product, only showing products where more than 10 units were sold in total.
  
• The output includes the ProductName and the TotalQuantity of units sold, aggregated from the Sales table.
  
• This helps identify which products are performing well in terms of sales volume, filtering out those with lower sales.

Example 2: Using COUNT Function with HAVING Clause

Let's write a query to identify products that have been sold more than once by counting the number of sales transactions from the Products and Sales tables.

Query:

SELECT p.ProductName, COUNT(s.SaleID) AS SaleCount
FROM Products p
JOIN Sales s ON p.ProductID = s.ProductID
GROUP BY p.ProductName
HAVING COUNT(s.SaleID) > 1;

Output:

Explanation:

• The output shows products with more than one sale, listing the product name and the total number of sales (SaleCount).
  
• In this case, Laptop has been sold twice, as shown by its SaleCount of 2.

Example 3: Using MIN Function with HAVING Clause

Let's write a query to find the first sale date for products that have sold more than 5 units by aggregating data from the Products and Sales tables

Query:

SELECT p.ProductName, MIN(s.SaleDate) AS FirstSaleDate
FROM Products p
JOIN Sales s ON p.ProductID = s.ProductID
GROUP BY p.ProductName
HAVING MIN(s.QuantitySold) > 5;

Output:

Explanation:

• The output lists products where the minimum quantity sold in any sale is greater than 5, along with the earliest sale date (FirstSaleDate) for each product.
  
• For each product, the FirstSaleDate represents the earliest date when the sales met this quantity condition.
  
• In the output, Headphones, Mobile Phones, and Tablet all had their first qualifying sales on the dates listed, with sales quantities exceeding 5 units.

Example 4: Using MAX Function with HAVING Clause

Let's write a query to determine the last sale date for products that have sold more than 15 units by aggregating sales data from the Products and Sales tables

Query:

SELECT p.ProductName, MAX(s.SaleDate) AS LastSaleDate
FROM Products p
JOIN Sales s ON p.ProductID = s.ProductID
GROUP BY p.ProductName
HAVING MAX(s.QuantitySold) > 15;

Output:

Explanation:

• The HAVING clause filters to only include products with a sale where the QuantitySold was more than 15.
  
• The output shows that the Mobile Phone is the only product with a sale where the quantity sold exceeded 15 units, and the last such sale occurred on 2023-08-02.

Conclusion

The PL/SQL HAVING clause is an essential tool for filtering grouped data based on aggregate functions. By combining it with the GROUP BY clause, we can apply conditions to grouped records, making it easier to extract meaningful insights from your data.

Whether we are using SUM, COUNT, MIN, or MAX functions, the HAVING clause offers flexibility and power in SQL queries.