SQL HAVING Clause

The HAVING clause in SQL is used to filter rows that appear after GROUP BY based on specified conditions. It is similar to the WHERE clause but operates on grouped rows rather than individual rows.

---

1. Overview

The HAVING clause is typically used in conjunction with the GROUP BY clause to filter groups based on aggregate values. It allows you to apply conditions to grouped data after the aggregation has been performed.

Example:

// Example of using the HAVING clause
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

This example selects the department and calculates the average salary for each department. It then filters the results to include only departments with an average salary greater than $50,000.

---

2. Syntax

The basic syntax of the HAVING clause is as follows:

SELECT column1, column2, ...
FROM table_name
GROUP BY column1, column2, ...
HAVING condition;

The HAVING clause must appear after the GROUP BY clause and before the ORDER BY clause, if present.

---

3. Usage

To use the HAVING clause, specify the condition(s) to filter grouped rows based on aggregate values. The condition can include aggregate functions such as COUNT(), SUM(), AVG(), MIN(), or MAX().

Example:

// Example of using the HAVING clause with aggregate functions
SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

This example groups the rows by the department column and calculates the count of employees in each department. It then filters the results to include only departments with more than 10 employees.

---

4. Conclusion

The SQL HAVING clause is a valuable tool for filtering grouped rows based on aggregate values. It allows users to apply conditions to grouped data after aggregation, enabling more precise analysis and reporting.