HAVING is a clause in SQL used to filter aggregate results. Unlike WHERE, it acts on grouped data after GROUP BY. 1. HAVING appears after GROUP BY and is used to filter the aggregation results, supporting aggregation functions such as AVG, COUNT, SUM, etc.; 2. Expressions or alias can be used (depending on the database support), but non-aggregation fields are not recommended; 3. Common scenarios include filtering a specific number of groups (such as customers with orders exceeding 10 orders), and cooperating with COUNT for record filtering (such as users who log in at least twice); 4. Support multi-condition combination filtering, such as meeting the average salary is greater than 5,000 and the number of people exceeds 20; 5. The difference from WHERE is that WHERE filters the original row before grouping, while HAVING filters the results after grouping, conditions that affect the data before grouping should be placed in WHERE to improve efficiency.

When using SQL for data aggregation, we often need to filter the aggregation results. At this time, the WHERE clause is no longer enough because it is used to filter the original rows. What is truly suitable for processing aggregate results is the HAVING clause.

What is HAVING?

HAVING is very similar to WHERE, but the object it acts as not the original data row, but the aggregate result after GROUP BY. You can understand it as "adding conditions to the data after the grouping".

For example: You want to know which departments have an average salary of more than 5,000 yuan. At this time, you have to first group by department, calculate the average salary of each department, and then select the departments whose average values ??meet the criteria. This "picking out" action should be HAVING.

Basic writing of HAVING

Structurally, HAVING appears after GROUP BY, followed by an expression:

 SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000;

The above SQL section says: "I want to look at the average salary of each department, but only those departments whose average salary is more than 5,000."

There are several details to note:

• HAVING can be followed by aggregate functions, such as SUM, COUNT, AVG, MAX, and MIN.
• You can also use alias, such as HAVING avg_salary > 5000 , but some databases (such as MySQL) support them, while others do not (such as early versions of PostgreSQL). It is recommended that it is safer to write expressions directly.
• It is not recommended to put non-aggregated fields in HAVING, which is the responsibility of WHERE.

Common usage scenarios

Filter a specific number of groups

For example, find customers with orders of more than 10 orders:

 SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 10;

This kind of scenario is very common, such as for user activity analysis, sales statistics, etc.

Use with COUNT for exclusion or retention

Sometimes we want to find customers who have no orders? In fact, this is more suitable for using LEFT JOIN IS NULL. So when will HAVING be used to combine COUNT? for example:

• Find users with at least two login records;
• Identify product categories with more than 3 subcategories;
• See which products are provided by multiple suppliers at the same time.

These need to be grouped first, then counted, and then filtered.

Multi-condition combination filtering

HAVING supports AND and OR to combine multiple conditions. For example:

 HAVING AVG(salary) > 5000 AND COUNT(*) > 20

It means: the average salary must exceed 5,000, and the number of people must exceed 20. To be considered as meeting the requirements.

The difference between HAVING and WHERE

Simply put:

• WHERE filters data before grouping, and it operates on the original row;
• HAVING is filtered after grouping, and operates on the aggregation results.

For example, you want to check the average salary of employees in a certain department, and the department number is greater than 10. The department numbering condition should be placed in WHERE, not HAVING:

 SELECT department_id, AVG(salary)
FROM employees
WHERE department_id > 10
GROUP BY department_id
HAVING AVG(salary) > 5000;

This is more efficient because the data range is reduced first and then the grouping calculations are performed.

If you put department_id > 10 in HAVING, although syntax may be fine, it is logically inappropriate and will also affect performance.

Basically that's it. Although HAVING seems to have only one more filtering step, it can help you quickly locate valuable aggregated data.

The above is the detailed content of Filtering aggregated results using the SQL HAVING clause.. For more information, please follow other related articles on the PHP Chinese website!