Oracle

The HAVING clause is used in SQL to filter records that work on aggregated data. Unlike the WHERE clause, which filters rows before aggregation, HAVING filters the results after the GROUP BY operation has been applied. This allows you to specify conditions on aggregate functions like SUM, COUNT, AVG, etc. It is particularly useful when you want to restrict the results of grouped data.

Here’s how you can effectively use the HAVING clause in Oracle SQL:

1. Basic Syntax

   • The HAVING clause is typically used with the GROUP BY clause.
   • General structure:

     SELECT column1, aggregate_function(column2)
     FROM table_name
     WHERE condition
     GROUP BY column1
     HAVING aggregate_condition;
     

2. Example Usage

   • For instance, if you have a table books with a column author_id and another column price, you can find authors with a total book price greater than a specified amount:

     SELECT author_id, SUM(price) AS total_price
     FROM books
     GROUP BY author_id
     HAVING SUM(price) > 100;
     

3. Multiple Conditions

   • You can use multiple conditions in the HAVING clause:

     SELECT author_id, COUNT(*) AS book_count, AVG(price) AS average_price
     FROM books
     GROUP BY author_id
     HAVING COUNT(*) > 5 AND AVG(price) < 20;
     

4. Order of Execution

   • Understand the order of SQL execution:
     1. FROM
     2. WHERE
     3. GROUP BY
     4. HAVING
     5. SELECT
     6. ORDER BY

5. Best Practices

   • Use HAVING for conditions that involve aggregate functions.
   • Ensure that HAVING is not misused for filtering individual rows; use WHERE for that.
   • Always combine it with GROUP BY for clarity and efficiency.

By following these guidelines, you can effectively use the HAVING clause to manipulate and query your data in Oracle SQL.

To gain complete access, login with gmail or outlook, no need of signup. click here

SELECT department,
       SUM(salary) AS department_salary
FROM org_employee
GROUP BY department
HAVING SUM(salary) > 250000;

SELECT b.employee_number,
       COUNT(a.order_number) AS order_count
FROM act_order a
INNER JOIN org_employee b ON b.employee_id = a.sales_agent_employee_id
GROUP BY b.employee_number
HAVING COUNT(a.order_number) > 1;

SELECT a.client_id, a.first_name, a.last_name,
       COUNT(b.order_number) AS order_count
FROM org_client a
INNER JOIN act_order b ON b.client_id = a.client_id
WHERE a.married_flag = 1
AND a.gender = 'F'
GROUP BY a.client_id, a.first_name, a.last_name
HAVING COUNT(b.order_number) > 1;

Example 1:

In the following example, we will demonstrate the application of WHERE, GROUP BY, and HAVING clauses. It's important to note that the WHERE clause is used before GROUP BY, and the HAVING clause follows GROUP BY. Also, the HAVING clause cannot be used without preceding it with GROUP BY.

Example 1 - Raw data from client table

GROUP BY query

In this example, we will ascertain the number of married female clients in each city, and then identify cities that have more than one married female client.

SELECT city ,
count(client_id)
FROM org_client
WHERE gender= 'F'
AND married_flag = 1
GROUP BY city
HAVING count(client_id) > 1

Step 1, Apply WHERE conditions

In the image above, the cells highlighted with a green background and white font represent those that satisfy both criteria of the WHERE condition (being married females).

Step 1 output after applying WHERE condition

Step 2 - Apply GROUP BY

In the provided image, we need to group the data by the 'city' column. As observed, Albany is represented by 2 client rows, while the other cities each have only one row.

Step 2 output, after applying GROUP BY

Step 3 - Apply HAVING Clause

Using the HAVING clause essentially means filtering the grouped rows based on the condition specified in the HAVING clause. In this example, we are looking for groups with a client count greater than one. The cells highlighted in green with white font are those that meet the condition set by the HAVING clause.

Final output of entire query