HAVING - (Oracle Clauses)

HAVING is a clause in SQL that is used to filter the records returned by a GROUP BY query based on a condition. It specifies a condition to be used to filter the results after the rows have been grouped together. The HAVING clause is used to filter group functions like SUM, COUNT, and AVG on the rows that are returned by the GROUP BY clause.

Syntax

The basic syntax of the HAVING clause is as follows:

SELECT column1, column2, ... FROM table_name
WHERE condition
GROUP BY column1, column2, ...
HAVING condition;

Here, the WHERE clause is used to filter the records before they are grouped, and the HAVING clause is used to filter the grouped records based on a condition.

Example

Consider the following table orders:

| order_id | order_date          | customer_id | total   |
|----------|---------------------|-------------|---------|
| 1        | 2021-05-01 12:00:00 | 1           | 50.00   |
| 2        | 2021-05-02 12:00:00 | 2           | 75.00   |
| 3        | 2021-05-05 12:00:00 | 2           | 100.00  |
| 4        | 2021-05-05 12:00:00 | 1           | 150.00  |
| 5        | 2021-05-10 12:00:00 | 3           | 250.00  |

To find the total sales made by each customer, we can use the following SELECT statement with the GROUP BY and HAVING clauses:

SELECT customer_id, SUM(total) as total_sales
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 100.00;

Output

| customer_id | total_sales |
|-------------|-------------|
| 1           | 200.00      |
| 2           | 175.00      |
| 3           | 250.00      |

Explanation

In the above example, we are selecting the customer_id and total columns from the orders table and grouping them by customer_id. We are then calculating the sum of the total column for each group and returning it as total_sales. We are also using the HAVING clause to filter the results to only include groups where the sum of the total column is greater than 100.00.

Use

The HAVING clause is used to filter the results of a GROUP BY query based on a condition. It is used to filter group functions like SUM, COUNT, and AVG.

Important Points

• The HAVING clause is used to filter the results of a GROUP BY query based on a condition.
• It is used to filter group functions like SUM, COUNT, and AVG.
• The HAVING clause is applied after the rows have been grouped together.

Summary

In summary, the HAVING clause is used to filter the results of a GROUP BY query based on a condition. It is used to filter group functions like SUM, COUNT, and AVG. The HAVING clause is applied after the rows have been grouped together, and it is used to filter the grouped results based on a condition.