MySQL beginners can say goodbye to the troubles of grouping and aggregation queries

1. Schematic diagram of group query

For the above raw data, group by DEPARTMENT_ID (employee id) and calculate the average value of SALARY (salary).

How should the above principle be written into code?

select 
	department_id,avg(salary)
from 
	test
group by 
	department_id;

It can be clearly seen that after grouping by department_id , the system will assign rows with the same department_id together by default. If you have several different department_id , they will be divided into several groups, and the number of data rows in each group may not necessarily be the same.

When the automatic allocation is completed, the intra-group operations will be performed according to the grouping function you wrote.

That is to say, when you use the sum() function, the sum will be calculated within the group; when you use the avg() function, the average will be calculated within the group; when you use the count() function, the count will be performed within the group; when you use the max() function, the maximum value will be calculated within the group; and when you use the min() function, the minimum value will be calculated within the group.

2. Detailed explanation of the syntax of the group by keyword

When learning MySQL, many newbies get stuck on group by keyword. So I hope I can use vernacular pictures and texts to help you truly understand the meaning of this keyword.

group by is a keyword used for grouping queries, and is generally used with sum(),avg(),count(),max(),min() aggregation functions. That is to say, as long as there is group by in the SQL statement, one or more of the aggregate functions (5 aggregate functions) will generally appear in the display fields after the select. Looking at the above picture, there is one thing you need to remember. After you group by field A in the table, you generally need to use aggregate functions on other fields in the table, which makes more sense, rather than using aggregate functions on field A, which doesn't make much sense.

Let’s think about the following question!

When group by is used in a SQL statement, there must be a field after the select that uses an aggregate function (5 aggregate functions). But besides this aggregate function, can any other fields be added after select?

The answer is definitely yes! However, this field has certain restrictions, and not all fields can be used. That is to say, when group by keyword is used in the SQL statement, the select can only be the fields that appear after group by , except for the aggregate function. That is, field A in the figure, only the fields after group by can exist after select.

3. A simple group query example

Example: Group by department number deptno and calculate the average salary of each department.

select 
    deptno,avg(sal) avgs
from 
    emp
group by 
    deptno

The results are as follows:

4. Pre-grouping and post-grouping screening

This knowledge point is to help everyone understand why we should use the where filter? When should I use having filter? This knowledge point is also a tricky thing for novices learning MySQL. Don’t worry, there is nothing you can’t learn if you follow Mr. Huang to learn MySQL.

1) The concept of original table and result set

The original table refers to the table that actually exists in the database. The original table information is queried using [select * from table name]. The result set refers to the table that is finally displayed to us after adding any other restriction conditions in the SQL statement. Adding different restrictions will result in different query result sets. There is only one original table, but the result sets are various.

2) Huang’s great advice

Whenever there is an aggregate function as a condition in the demand, it must be filtered after grouping. If pre-grouping screening is possible, give priority to pre-grouping screening. (Considering performance issues)

3) Case Study

The original dataset is as follows:

① Screening before grouping

Exercise 1: Find the sum of the salaries of each department whose names contain the letter S.

Exercise 2: Query the average salary of different departments with a salary greater than 2000.

② Screening after grouping

Exercise 1: Query the department number and number of employees in departments where the number of employees is greater than 3.

Exercise 2: Query the department number and maximum salary of each department whose maximum salary is greater than 3000.

③ Combined use of pre-grouping screening and post-grouping screening

Exercise: Find the department numbers and average values ​​of the employees who joined in 1981 and whose average salary among different departments is greater than 2000.

5. Group query (group by function)

Exercise: Group employees by the length of their names, find the number of employees in each group, and filter out which ones have a number of employees > 3?

select length(ename) len,count(*) counts
from emp
group by len
having counts > 3;

The results are as follows:

6. Group query (group by multiple fields)

Exercise: Query the average salary of employees in each department and each job type.

7. Group by and order by, an old pair

Exercise 1: Query the average salary of employees in each department and sort them in descending order by average salary.

Exercise 2: Query the average salary of employees in each department and sort them in ascending order by average salary.

8. Summary of group query

1) The grouping function is used as a condition and must be placed in the having clause.

2) If pre-grouping screening is possible, give priority to using it. ( where filter)

3) group by clause supports single field grouping, multiple field grouping (multiple fields are separated by commas and there is no order requirement), and function grouping (which is less commonly used).

The above is the detailed content that MySQL beginners can say goodbye to the troubles of grouped aggregate queries. For more information about MySQL grouped aggregate queries, please pay attention to other related articles on 123WORDPRESS.COM!