Filtering Groups with HAVING
The HAVING clause in SQL is used to filter the results after the grouping operation of GROUP BY. It is similar to the WHERE clause, but the key difference is that WHERE filters rows before grouping, whereas HAVING filters after the groups have been formed.
Key Points:
- The HAVING clause is typically used with aggregate functions (COUNT(), SUM(), AVG(), etc.) to filter groups based on the results of those functions.
- HAVING is used after GROUP BY and allows you to set conditions on aggregate functions.
Syntax:
SELECT column1, column2, ..., aggregate_function(column_name) FROM table_name WHERE condition GROUP BY column1, column2, ... HAVING aggregate_function(column_name) condition;
aggregate_function(column_name): The function used to perform calculations on the grouped data (e.g., COUNT(), SUM(), AVG()).
HAVING condition: A condition applied to the result of the aggregate function.
Examples of Using HAVING
Example 1: Filtering Groups with COUNT()
SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department HAVING COUNT(*) > 5;
This query counts the number of employees in each department and filters out departments that have fewer than 6 employees.
Example 2: Filtering Groups with SUM()
SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department HAVING SUM(salary) > 100000;
This query sums the salaries of employees in each department and filters out departments where the total salary is less than $100,000.
Example 3: Filtering Groups with AVG()
SELECT department, AVG(salary) AS average_salary FROM employees GROUP BY department HAVING AVG(salary) > 50000;
This query calculates the average salary for employees in each department and filters out departments where the average salary is less than $50,000.
Example 4: Using Multiple Conditions in HAVING
SELECT department, COUNT(*) AS employee_count, AVG(salary) AS average_salary FROM employees GROUP BY department HAVING COUNT(*) > 3 AND AVG(salary) > 60000;
This query groups employees by department and filters out departments that do not meet both conditions: having more than 3 employees and an average salary greater than $60,000.
Conclusion
The HAVING clause is a powerful SQL tool for filtering grouped data based on aggregate values. It helps you refine your results after applying GROUP BY and ensures that only relevant groups are included in your query output.