MySQL

In MySQL, the HAVING clause is used to filter the results of an aggregated query. It is similar to the WHERE clause, but HAVING is applied after the aggregation has occurred, allowing you to filter groups of data. The HAVING clause is particularly useful when combined with GROUP BY to filter based on aggregate functions such as COUNT(), SUM(), AVG(), and MAX().

Here’s a detailed guide on using the HAVING clause in MySQL, along with examples for new students:

1. Basic Syntax of HAVING:

   • The HAVING clause is used to filter groups that are created by the GROUP BY clause. It allows you to filter based on the results of aggregate functions.
   • Syntax:

   SELECT department_id, COUNT(*) AS employee_count 
   FROM employees 
   GROUP BY department_id 
   HAVING employee_count > 5;

   • This query returns departments with more than 5 employees.

2. Difference Between WHERE and HAVING:

   • WHERE filters rows before the aggregation, while HAVING filters groups after the aggregation.
   • Example:

   SELECT department_id, AVG(salary) AS avg_salary 
   FROM employees 
   WHERE salary > 30000 
   GROUP BY department_id 
   HAVING avg_salary > 50000;

   • In this query, WHERE filters individual rows where the salary is greater than 30,000, and HAVING filters departments where the average salary is greater than 50,000.

3. Using HAVING with Aggregate Functions:

   • The HAVING clause is typically used with aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX().
   • Example:

   SELECT department_id, SUM(salary) AS total_salary 
   FROM employees 
   GROUP BY department_id 
   HAVING total_salary > 200000;

   • This query returns departments where the total salary exceeds 200,000.

4. Using HAVING with GROUP BY:

   • HAVING is used most often in conjunction with the GROUP BY clause to filter groups after aggregation.
   • Example:

   SELECT manager_id, COUNT(*) AS num_employees 
   FROM employees 
   GROUP BY manager_id 
   HAVING num_employees > 10;

   • This query returns managers who have more than 10 employees reporting to them.

5. Using HAVING with Multiple Conditions:

   • You can use HAVING with multiple conditions by combining them with AND or OR.
   • Example:

   SELECT department_id, AVG(salary) AS avg_salary 
   FROM employees 
   GROUP BY department_id 
   HAVING avg_salary > 50000 
   AND department_id IN (1, 2, 3);

   • This query returns departments 1, 2, and 3 where the average salary is greater than 50,000.

6. Using HAVING Without GROUP BY:

   • In some cases, you can use HAVING without GROUP BY, especially if you're using aggregate functions over the entire result set.
   • Example:

   SELECT SUM(salary) AS total_salary 
   FROM employees 
   HAVING total_salary > 1000000;

   • This query checks if the total salary of all employees exceeds 1,000,000.

7. Combining HAVING with ORDER BY:

   • You can combine HAVING with ORDER BY to sort the results after filtering the groups.
   • Example:

   SELECT department_id, COUNT(*) AS employee_count 
   FROM employees 
   GROUP BY department_id 
   HAVING employee_count > 5 
   ORDER BY employee_count DESC;

   • This query returns departments with more than 5 employees, sorted by the number of employees in descending order.

8. Performance Considerations:

   • Queries using HAVING can be resource-intensive, especially on large datasets, since the filtering occurs after the aggregation. Indexes on columns used in GROUP BY can help optimize performance.

The HAVING clause is essential for filtering results in aggregate queries, especially when used with GROUP BY. It allows you to filter data based on aggregate calculations, providing more control over grouped data and results.

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