MySQL HAVING clause

➔ The HAVING clause must be used to filter results based on aggregate functions, because the WHERE clause cannot process aggregate functions.

➔ The HAVING clause is almost always used with aggregate functions such as SUM(), COUNT(), AVG(), MIN(), and MAX().

➔ WHERE filters the rows before GROUP BY operation, while HAVING filters the results after GROUP BY operation.

➔ The HAVING clause must come after the GROUP BY clause and before any ORDER BY clause.

➔ Multiple conditions can be applied in the HAVING clause using AND, OR, and NOT.

HAVING clause syntax

SELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
WHERE condition -- Optional: filters rows before grouping
GROUP BY column_name
HAVING aggregate_condition -- Filters the groups created by GROUP BY
ORDER BY column_name; -- Optional

Filtering by Count

Example: Find positions that have fewer than 5 employees.

SELECT job_id, COUNT(employee_id) 
FROM employees 
GROUP BY job_id 
HAVING COUNT(employee_id) < 5;

Filtering by Average:

Example: Find positions that have average salary less than 5000.

SELECT job_id, AVG(salary) 
FROM employees 
GROUP BY job_id 
HAVING AVG(salary) < 5000;

Filtering by Sum:

Example: Find the positions and the number of employees working in those positions where the total salary is more than 10,000.

SELECT job_id, COUNT(*) AS total_employees, SUM(salary) As total_spent
FROM employees 
GROUP BY job_id 
HAVING SUM(salary) > 10000;

Using WHERE clause and HAVING clause together

The WHERE clause first filters specific rows, then the HAVING clause filters their aggregate results.

Example: Find the department IDs and their total salaries whose department_id is greater than 50 and total salary is greater than 15,000.

SELECT department_id, SUM(salary) AS TotalSalary
FROM employees
WHERE department_id > 50 -- Filters individual department_id
GROUP BY department_id
HAVING SUM(salary) > 15000; -- Then filters the aggregate results SUM(Salary).


--HAVING clause with column aliases
SELECT department_id, SUM(salary) AS TotalSalary
FROM employees
WHERE department_id > 50 
GROUP BY department_id
HAVING TotalSalary > 15000;

HAVING clause with Multiple Conditions

Example: Find the department IDs, their total salaries, and the number of employees whose total salary is more than 20,000 and the total number of employees is more than 5.

SELECT department_id, SUM(salary) AS TotalSalary, COUNT(*)
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 20000 AND COUNT(*) > 5;

A HAVING clause without a GROUP BY clause

The query will return at most one row.

In MySQL, it is syntactically valid to use the HAVING clause without the GROUP BY clause.

Using the HAVING clause without the GROUP BY clause treats the entire table as a single implicit group.

Example: If the total salary exceeds a certain number, calculate the total salary across the entire table.

SELECT SUM(salary) 
FROM employees 
HAVING SUM(salary) > 5000;

Example: If the average salary exceeds a certain number, determine the average salary of the entire table.

SELECT AVG(salary) 
FROM employees 
HAVING AVG(salary) > 1000;