What is the use of “having” in Oracle?

In Oracle, the HAVING clause is used to filter the results of grouped data after the GROUP BY clause. The HAVING clause can include aggregate functions and logical operators to filter out groups that meet specific conditions. Its syntax is as follows:

SELECT column1, column2, ...
FROM table
GROUP BY column1, column2, ...
HAVING condition;

Condition is an expression used to filter the grouped results. This expression can combine aggregate functions (such as COUNT, SUM, AVG, etc.) and logical operators (such as AND, OR, NOT, etc.) to determine the groups that meet the condition.

For example, let’s say we have a product table that includes product names and prices. We want to find products with prices greater than 100. We can use the following SQL query:

SELECT product_name, SUM(price) as total_price
FROM products
GROUP BY product_name
HAVING SUM(price) > 100;

This query first groups items by their names, then calculates the total price for each group. It then filters out groups with a total price greater than 100 using the HAVING clause, and finally returns the names of the items and their total prices that meet the condition.

In summary, the HAVING clause in Oracle is used to filter the grouped results, and conditions can be defined using aggregate functions and logical operators.