What is the usage of the “having” clause in SQL statements?

The HAVING clause in SQL is used to filter the results of the GROUP BY clause. It allows you to filter the query results based on the results of aggregate functions. The HAVING clause is typically used when using GROUP BY in a query to filter the grouped results.

The syntax of the HAVING clause is as follows:

SELECT 列名1, 列名2, ...
FROM 表名
WHERE 条件
GROUP BY 列名1, 列名2, ...
HAVING 条件

In the above syntax, the HAVING clause comes after the GROUP BY clause and can use aggregate functions (such as SUM, COUNT, AVG, etc.) to filter results. Only groups that meet the HAVING condition will be included in the query results.

Here is an example: let’s say we have a table of employees and we want to find the department where the average salary is greater than 5000.

SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 5000;

In the example above, we first group by department, calculate the average salary for each department, and name it as avg_salary. Finally, we filter out departments with an average salary greater than 5000 using the HAVING clause.

Note: The HAVING clause is only applicable to the aggregate function results in the GROUP BY clause. If the GROUP BY clause is not used, the HAVING clause will be considered invalid.