HAVING Clause in SQL

The HAVING clause in SQL is used to filter grouped records based on a condition, typically after applying an aggregate function. It’s like a WHERE clause but specifically designed to work with grouped data created by the GROUP BY clause.

Key Points

1. Filters Groups:
   HAVING filters the results of a GROUP BY query. Without GROUP BY, it behaves similarly to a WHERE clause but applies to aggregates.

2. Works with Aggregate Functions:
   Unlike WHERE, which filters individual rows, HAVING is used to filter based on aggregate values, such as SUM(), AVG(), or COUNT().

3. Position in Query:
   The HAVING clause comes after the GROUP BY clause and before the ORDER BY clause.

Syntax

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

Examples

1. Using HAVING to Filter Aggregated Results

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

• Groups employees by department, calculates the average salary for each, and returns only departments with an average salary greater than 50,000.

2. Combining WHERE and HAVING

SELECT department, SUM(salary) AS total_salary
FROM employees
WHERE job_title = 'Engineer'
GROUP BY department
HAVING SUM(salary) > 200000;

• Filters rows where the job title is 'Engineer', groups them by department, and then filters groups with a total salary greater than 200,000.

3. Using COUNT() with HAVING

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

• Groups employees by department and returns only departments with more than 10 employees.

4. Multiple Conditions in HAVING

SELECT department, MAX(salary) AS max_salary
FROM employees
GROUP BY department
HAVING MAX(salary) > 100000 AND COUNT(*) > 5;

• Filters for departments where the highest salary exceeds 100,000 and there are more than 5 employees.

When to Use the HAVING Clause

• To filter grouped data after applying an aggregate function.

• To apply conditions that cannot be handled by the WHERE clause, such as SUM() > 10000 or AVG() < 50.

Common Mistakes to Avoid

1. Using HAVING Without GROUP BY:

    SELECT COUNT(*)
    FROM employees
    HAVING COUNT(*) > 10; -- Works but unnecessary; use WHERE instead.
   

2. Mixing WHERE and HAVING Incorrectly:

    SELECT department, SUM(salary)
    FROM employees
    WHERE SUM(salary) > 50000 -- Error!
    GROUP BY department;
   

   • Aggregate functions cannot be used in WHERE. Use HAVING for such conditions.

Tips for Beginners

• Use WHERE to filter rows before grouping.

• Use HAVING to filter groups after aggregation.

• Practice pairing HAVING with aggregate functions to understand its role in filtering grouped data.

For example:

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition -- Filters individual rows
GROUP BY column1
HAVING condition -- Filters grouped results
ORDER BY column1; -- Orders the final results