sql分组求平均值_SQL-汇总分析

大纲

1.汇总分析

2.分组

3.对分组结果指定条件

4.用sql解决业务问题

5.对查询结果排序

6.如何看懂报表信息

一,汇总分析

• 汇总函数

汇总函数 作用

count 求某列的行数

sum 对某列数据求和

avg 求某列数据的平均值

max 求某列数据的最大值

min 求某列数据的最小值

• 函数的三个功能

功能 输入 输出

count函数

1.count（选择列名）

会自动排除null

count（全部列）不会排除null

2.求和（sum）

3.求某列数据平均值

4.求某列数据的最大值，最小值

如果计算之前，不想计算重复值，就可以把重复值删除

二，分组

sql分组:group by

分组方法：

1.数据分组

2.应用函数

3.组合结果

2.select 性别,count(*)
3.from student
1.group by 性别;

运行顺序：

1.从哪张表查找数据

2.查询条件

3.对第二步查询出的数据进行分组

4对分组应用函数，并组合结果

三，对分组结果指定条件（having）

select 性别,count(*)
from student
group by 性别
-- 对分组结果指定条件
having count(*)>1;

四，如何用sql解决业务问题

步骤：

1.翻译成大白话

2.写出分析思路

3.写出对应的sql子句

1.翻译大白话

问题：

如何计算各科的平均成绩

=如何计算每门课程的平均成绩

2.写出分析思路

select 查询结果[每门课的课程号:分组，平均成绩:avg(成绩)]
from 从哪张表中查找数据[成绩表:score]
where 查询条件[没有]
group by 分组[每门课程：按课程号分组]
having 对分组结果指定条件;[没有]

五，对查询结果排序

对查询结果排序 order by

降序（desc）:从大到小

升序（asc）：从小到大

limit 从查询结果中取出指定行

六，如何看懂报错信息？

常见错误：

1.在group by 里使用了select的别名

因为group by在select之前运行，所以如果设置别名，group by是识别不出的。

2.在where中使用聚合函数

3.字符串和数字的排序是不一样的，在排序之前需要把表格中的数字转换为数值格式

练习：

一，汇总分析

1.查询课程编号为“0002”的总成绩

select sum(成绩)
from score
-- 设定指定查询条件
where 课程号="0002";

2.查询选了课程的学生人数

select count(学号)
from score;

二，分组

1.查询各科成绩最高和最低的分

select 课程号,max(成绩),min(成绩)
from score
group by 课程号;

2.查询每门课被选修的学生人数

select 课程号,count(学号)
from score
group by 课程号;

3.查询男生，女生人数

select 性别,count(姓名)
from student
group by 性别；

三，对分组结果指定条件（having）

1.查询平均成绩大于60分学生的学号和平均成绩

select 学号,avg(成绩)
from score
group by 学号
having avg(成绩)>60;

2.查询至少选择两门课程的学生学号

step1：计算每个学生选修课程数

step2：统计选择至少两门课程的学生数

select count(课程号),学号
from score
group by 学号
having count(课程号)>=2;

3.查询同姓名学生名单并统计同名人数

step1：知道每一个学生姓名（对姓名分组）

step2：统计同名的人数

select 姓名,count(*) as 人数
from student
group by 姓名
having count(*)>=2;

四，用sql解决业务问题

练习：如何计算每门课程的平均成绩，并且平均成绩大于等于80分

分析思路：
select 查询结果[每门课的课程号:分组，平均成绩:avg(成绩)]
from 从哪张表中查找数据[成绩表:score]
where 查询条件[没有]
group by 分组[每门课程：按课程号分组]
having 对分组结果指定条件;[没有]

sql语句：
select 课程号,avg(成绩)
from score
group by 课程号
having avg(成绩)>=80;

五，对查询结果进行排序

1.查询不及格的课程并按课程号从大到小排列

select count(课程号),成绩
from score
where 成绩<=60
order by 课程号 desc；

2.查询每门课程的平均成绩，结果按照平均成绩升序排序。平均成绩相同时，按课程号降序排序

select 课程号,avg(成绩) as 平均成绩
from score
group by 课程号
order by 平均成绩 asc,课程号 desc;

SQLZOO习题

错题

select from nobel prize

14.查找1984年获奖者和主题按主题和获胜者名称排序，并把物理奖和化学奖排到最后面显示

select winner,subject
from nobel
where yr=1984
order by subject in ("Physics","Chemistry"),subject,winner;

subject in ("Physics","Chemistry")对subject做一个if的判断，有的是1，没有的是0，

而1在0后面，所以物理和化学奖排到最后面显示

sum and count

7.显示每个大陆的国家数量，并且这些国家里人口数量至少为1000万

分析思路：
#首先确定人口数量至少为1000万的国家，然后每个大陆就意味着按照大陆分组
所以先限定条件，再按大陆分组

select continent,count(name)
from world
where population >=10000000
group by continent

8.列出每个洲名称，并且每个洲人口要大于等于1个亿

分析思路：
#按照州先分组，然后限定每个洲的人口要大于等于1个亿

select continent,count(name)
from world
group by continent
having sum(population)>=100000000;