How to group and sum in mysql

In mysql, you can use the SELECT statement with the SUM() function and "GROUP BY" to perform group sums. The syntax is "SELECT query field SUM (summation field) AS field FROM table name WHERE condition GROUP BY Grouping field one, grouping field two;".

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How to perform group sum in mysql

In mysql, you can use

SELECT 查询字段 SUM(求和字段) AS 字段 FROM 表名 WHERE 条件 GROUP BY 分组字段一,分组字段二;

to perform group sum.

Examples are as follows:

1) count() finds the number of rows in a certain column

It is easy to understand, count(column name) is to find the number of rows in a certain column The number of rows in the column. It is worth noting that this column does not count the number of rows with null values. Example:

>-- Query how many teachers there are

>-- Query how many rows there are in the teacher table SELECT COUNT(*) FROM teacher;

If there are duplicate values ​​in the column, we count and don’t want to count duplicate values, what should we do? We can use distinct to solve:

-- 查询成绩表里共有几个学生的成绩
SELECT COUNT(DISTINCT 学号) FROM score;

2) sum() sums a certain column of data

sum, that is To sum the values ​​in a column, only numerical values ​​can be calculated. Example:

--  对所有成绩求和
SELECT SUM(成绩) FROM score;

3) avg() averages the values ​​in a certain column

Similarly, the average can only be calculated Calculate numerical values:

-- 对所有成绩求平均值
SELECT AVG(成绩) FROM score;

##4) max() finds the maximum value of a certain column of data, min() finds the minimum value of a certain column of data

-- 获取所有成绩中的最大成绩
SELECT MAX(成绩),MIN(成绩) FROM score;

Group

In the question, we mentioned that we need to know the average score of each subject, then we need to group it.

In SQL, the group by statement can group the result set according to one or more columns. Example:

-- 计算每一科课程的平均成绩
SELECT 课程号,AVG(成绩) FROM score GROUP BY 课程号;

In the previous article, we briefly introduced the execution order of SQL statements: which table is executed first Get the data and then execute the select statement;

Here we get the data from the

score table and use the group by statement to group the data according to certain rules; then in the group Calculate count() on the results; finally select the combined results and summarize each set of calculation results in the previous step into a table.

Specify conditions for grouping conditions

In the first question, it is required to "calculate the average score of each subject and get the average score to be greater than or equal to 80 points". In the above two sections, we got the calculation The average score of a subject every day, now specify the condition for the average score

: "greater than or equal to 80 points" , we use the having clause.

The having clause and the where clause are both conditional selection of data. The difference is that where cannot be used with the summary function.

-- 计算平均成绩大于等于80分的课程;
SELECT 课程号,AVG(成绩) FROM score GROUP BY 课程号 HAVING AVG(成绩)>=80;

Does that mean that the order of the having clause is also in front of the summary of select results? Yes, the having clause specifies conditions for the grouping results of group by, such as requiring a score of greater than or equal to 80. After filtering out the results, the results are summarized.

-- 对教师表根据教师姓名排序
SELECT * FROM teacher ORDER BY 教师姓名;

We found that the null value Null is ranked first, so:

Columns containing null values ​​are sorted when sorting , the null value will be at the beginning, When the amount of data is large and you want to see the null value, you can use this sorting.

另外，在补充一个limit语句，从查询结果中取出指定行，比如，我们如果从刚才的查询结果中只取第一行：

-- 计算平均成绩大于等于80分的课程并根据成绩降序排,并获取第一行
SELECT 课程号,AVG(成绩) FROM score GROUP BY 课程号 HAVING AVG(成绩)>=80 ORDER BY AVG(成绩) DESC LIMIT 1;

接下来我们进行一个总结。

用SQL解决业务问题分析思路

简单来说：明确问题，将问题翻译成大白话，逐步进行拆解，并写出分析思路和对应的SQL思路。

1） 明确问题，将问题翻译成大白话：

“计算各科的平均成绩并且得出平均成绩大于等于80分的课程并降序排列”翻译成大白话就是：

“计算每一门课程的平均成绩，然后根据得出的结果，进行大于等于80分的条件查询，对查询结果进行降序排列”。

2） 逐步进行拆解，并写出分析思路和对应的SQL思路：

① 对课程号进行分组，计算每一门课程的平均成绩；

② 对①的结果指定条件“>=80”;

③ 对②的结果进行降序排列；

我们可以把拆解后的思路套用到下方的公式中：

select 查询结果
from 从那张表中查找数据
where 查询条件（运算符、模糊查询）
group by 分组（每个）
having 对分组结果指定条件
order by 对查询结果排序
limit 从查询结果中取出指定行;

试一下吧：

select 查询结果[课程号，avg(成绩）]
from 从那张表中查找数据[成绩表score]
where 查询条件（运算符、模糊查询）[No]
group by 分组（每个）[课程号]
having 对分组结果指定条件[avg(成绩）>=80]
order by 对查询结果排序[avg(成绩）desc]
limit 从查询结果中取出指定行;[No]

这样子，问题是不是就迎刃而解呢？

推荐学习：mysql视频教程

The above is the detailed content of How to group and sum in mysql. For more information, please follow other related articles on the PHP Chinese website!