GROUP BY is used to classify and summarize data, and HAVING is used to filter grouping results. GROUP BY Groups data in a unique combination of one or more columns and performs an aggregation operation. The non-aggregation field in SELECT must appear in GROUP BY. When grouping multiple fields, the combination must be unique, and the order does not affect the result. HAVING is used to filter the results after grouping, and acts on the aggregated data and cannot replace WHERE. The latter is used for row filtering before grouping. Common errors include using fields that do not appear in SELECT or GROUP BY in HAVING, or misuse of HAVING instead of WHERE to affect performance. In actual applications, you can combine the conditions for use, such as finding departments with employees greater than 2 and average salary higher than 5,000: SELECT department_id, COUNT() AS employee_count, AVG(salary) AS avg_salary FROM employees GROUP BY department_id HAVING COUNT() > 2 AND AVG(salary) > 5,000; it is recommended to avoid complex expressions in HAVING, pay attention to the limitations of alias use, and give priority to using WHERE to reduce the amount of data and improve efficiency. Multi-field grouping and HAVING can be used to filter specific combinations, such as a position appears more than 3 times in a certain department: SELECT department_id, job_title, COUNT() FROM employees GROUP BY department_id, job_title HAVING COUNT() > 3. Understanding the difference and collaboration between GROUP BY and HAVING will help write more efficient aggregation queries.

SQL's GROUP BY and HAVING clauses are very core tools in data analysis. Many people will use them as basic statistics, but only by truly mastering their application logic can they be at ease in complex queries.

The Essence of GROUP BY: Category Summary

Many people understand GROUP BY as "grouping by a certain field", but this is just the surface. Its essence is to divide the data into different groups according to a unique combination of one or more columns, and then perform an aggregation operation on each group .

For example, you want to know the average salary of each department:

 SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;

This sentence SQL classifies the employee table by department_id , and then calculates the average salary for each category.

Pay attention to several key points:

• All non-aggregated fields in SELECT must appear after GROUP BY .
• If you add job_title to SELECT, you must also add GROUP BY.
• When grouping multiple fields, the order is not important, but the combination must be unique.

HAVING is the result after filtering grouping

Many people confuse WHERE and HAVING . Simply put:

• WHERE is to filter raw data rows and work before grouping;
• HAVING is the result of filtering the grouping and works after grouping.

For example, if you want to find a department with a total salary of more than 10,000:

 SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 10000;

WHERE cannot be used here because WHERE cannot access the results of the aggregate function.

Common errors:

• Fields that do not appear in SELECT or GROUP BY are used in HAVING (unless supported by the database).
• It is for granted that HAVING can replace WHERE, but its performance may be much worse.

Tips and precautions in practical applications

Sometimes business needs are more complicated. For example, if you want to find a department with "more than two employees and an average salary of more than 5,000", you need to combine multiple conditions:

 SELECT department_id, COUNT(*) AS employee_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 2 AND AVG(salary) > 5000;

Some practical suggestions:

• Try to avoid using complex expressions in HAVING, which affects readability.
• Be careful when using alias, some databases do not allow direct reference of alias in SELECT in HAVING.
• When you need to filter first and then group, use WHERE first to reduce the amount of data and improve efficiency.

Another case is that multi-field grouping is combined with HAVING to filter specific combinations. For example, you want to find out if a position occurs more than 3 times in a certain department:

 SELECT department_id, job_title, COUNT(*)
FROM employees
GROUP BY department_id, job_title
HAVING COUNT(*) > 3;

This structure is common in report development.

Basically that's it. Understanding the difference and collaboration between GROUP BY and HAVING can allow you to write clearer and more efficient aggregate queries.

The above is the detailed content of Mastering SQL GROUP BY and HAVING Clause Applications. For more information, please follow other related articles on the PHP Chinese website!