【SQL】常用函数AVG、SUM、MAX、MIN、COUNT

最新推荐文章于 2024-03-03 12:01:33 发布
最新推荐文章于 2024-03-03 12:01:33 发布
阅读量4.5k 收藏 12
点赞数 2
分类专栏： SQL 文章标签： 数据库 oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u012778985/article/details/112470996
版权

目录

常用函数

说明：

 测试

AVG平均值

SUM总计值

MAX最大值

MIN最小值

COUNT(*)总数

NVL空值转换

GROUP BY创建数据组

ORDER BY排序

HAVING子句

SQL语句的编译顺序

函数嵌套

特例

常用函数

说明：

AVG([distinct|all] XXX):意思是计算XXX的平均值

SUM([distinct|all] XXX):意思是计算XXX的总计值

MAX([distinct|all] XXX):意思是计算XXX的最大值

MIN([distinct|all] XXX):意思是计算XXX的最小值

COUNT(*[distinct|all] XXX):意思是计算表中数据的总数

使用组函数的时候，空值被自动忽略count(*)除外，但是可以用nvl和nvl2进行空值转换

 测试

先查看emp表中的数据

SQL> show user;
USER is "SCOTT"
SQL> select * from emp;
 
 EMPNO ENAME      JOB         MGR HIREDATE                   SAL  COMM  DEPTNO
------ ---------- --------- ----- ------------------- ---------- ----- -------
  7369 SMITH      CLERK      7902 1980-12-17 00:00:00        800            20
  7499 ALLEN      SALESMAN   7698 1981-02-20 00:00:00       1600   300      30
  7521 WARD       SALESMAN   7698 1981-02-22 00:00:00       1250   500      30
  7566 JONES      MANAGER    7839 1981-04-02 00:00:00       2975            20
  7654 MARTIN     SALESMAN   7698 1981-09-28 00:00:00       1250  1400      30
  7698 BLAKE      MANAGER    7839 1981-05-01 00:00:00       2850            30
  7782 CLARK      MANAGER    7839 1981-06-09 00:00:00       2450            10
  7788 SCOTT      ANALYST    7566 1987-04-19 00:00:00       3000            20
  7839 KING       PRESIDENT       1981-11-17 00:00:00       5000            10
  7844 TURNER     SALESMAN   7698 1981-09-08 00:00:00       1500     0      30
  7876 ADAMS      CLERK      7788 1987-05-23 00:00:00       1100            20
  7900 JAMES      CLERK      7698 1981-12-03 00:00:00        950            30
  7902 FORD       ANALYST    7566 1981-12-03 00:00:00       3000            20
  7934 MILLER     CLERK      7782 1982-01-23 00:00:00       1300            10
 
14 rows selected.

AVG平均值

计算员工的平均工资、平均奖金

SQL>  select avg(sal) 平均工资，avg(comm) 平均奖金 from emp;
 
  平均工资   平均奖金
---------- ----------
2073.21429        550

由于comm中含有null因此计算的结果只有四个人的平均工资

 

SUM总计值

计算员工的总工资、总奖金

SQL> select sum(sal) 总工资,sum(comm) 总奖金 from emp;
 
    总工资     总奖金
---------- ----------
     29025       2200

 

同样的，这个总奖金依然是四个人的总奖金，因为null不能和任何值做比较和运算

 

MAX最大值

计算员工的最高工资，最高奖金

SQL> select max(sal) 最高工资,max(comm) 最高奖金 from emp;
 
  最高工资   最高奖金
---------- ----------
      5000       1400

 

MIN最小值

计算员工的最低工资、最低奖金

SQL>  select min(sal) 最低工资,min(comm) 最低奖金 from emp;
 
  最低工资   最低奖金
---------- ----------
       800          0

由此可见在AVG、SUM、MAX、MIN中null是不参与运算的，也就是说被忽略了。

 

COUNT(*)总数

计算部门总数、员工总数、和有奖金的人数。

SQL> select count(distinct deptno) 部门总数,count(deptno) "部门总数？",count(empno) 员工总数 from emp;
 
  部门总数 部门总数？   员工总数
---------- ---------- ----------
         3         14         14

由此可见，在使用count(XXX) 的时候不能去掉重复值

那么count(*)呢

SQL> select count(empno) a,count(*) b,count(9999) c,count('XXX') d,count(comm) e,count(COMM) f,count('comm') g,count('COMM') h from emp;
 
  A   B   C   D   E   F   G   H
--- --- --- --- --- --- --- ---
 14  14  14  14   4   4  14  14

由此可见count('XX')单引号中的值都被当成了任意值，也就是说被当成了count(*)。

SQL> select count("comm") i,count("COMM") j from emp;
select count("comm") i,count("COMM") j from emp
             *
ERROR at line 1:
ORA-00904: "comm": invalid identifier

为啥报错？

SQL中只支持单引号，表示字符串常量

SQL中的双引号用于表示字符串

 

NVL空值转换

按照总人数计算平均奖金

SQL>  select count(nvl(comm,0)) 总人数,sum(nvl(comm,0)) 总奖金,avg(nvl(comm,0)) 总人数平均奖金 from emp;
 
    总人数     总奖金 总人数平均奖金
---------- ---------- --------------
        14       2200     157.142857

nvl(comm,0)的意思是将comm中的null转换成0，也可以改成其他值，需要根据实际情况来定。

 

GROUP BY创建数据组

用于结合聚合函数，根据一个或多个列对结果集进行分组

直接看例子

计算各个部门的总工资，总人数，平均工资。

SQL> select deptno 部门编号,sum(sal) 总工资,count(empno) 总人数, avg(sal) 平均工资 from emp group by deptno;
 
  部门编号     总工资     总人数    平均工资
---------- ---------- ---------- ----------
        30       9400          6 1566.66667
        20      10875          5       2175
        10       8750          3 2916.66667

结果按照部门编号进行分别运算。

select 之后，from 之前出现的字段，要么是被用于组函数，要么是被用于分组(即用在group by 后面，不过也可以在 select 后不出现)

group by 子句用于 where 条件之后，即可以在分组之前将不合条件的数据过滤掉

group by 在 where 之后，select 之前被编译，所以不能在 group by 中使用列的别名 Oracle 9i 中，分组之后的结果(默认)会按照用于分组的字段进行升序排列;但是 Oracle  10g 中不会进行此排序，需要特别指定才可以

 

也可以用多列进行分组

计算各部门，各岗位的总工资，总人数和平均工资

SQL> select deptno 部门编号,job 岗位,sum(sal) 总工资,count(empno) 总人数, avg(sal) 平均工资 from emp group by deptno,job;
 
  部门编号 岗位          总工资     总人数      平均工资
---------- --------- ---------- ---------- ----------
        20 CLERK           1900          2        950
        30 SALESMAN        5600          4       1400
        20 MANAGER         2975          1       2975
        30 CLERK            950          1        950
        10 PRESIDENT       5000          1       5000
        30 MANAGER         2850          1       2850
        10 CLERK           1300          1       1300
        10 MANAGER         2450          1       2450
        20 ANALYST         6000          2       3000
 
9 rows selected.

 

ORDER BY排序

如果觉得上述计算结果比较杂乱的话可以使用order by对其进行排序，默认升序

将刚才的结果按照部门编号升序排序

SQL>  select deptno 部门编号,job 岗位,sum(sal) 总工资,count(empno) 总人数, avg(sal) 平均工资 from emp group by deptno,job order by 部门编号;
 
  部门编号 岗位          总工资     总人数        平均工资
---------- --------- ---------- ---------- ----------
        10 CLERK           1300          1       1300
        10 MANAGER         2450          1       2450
        10 PRESIDENT       5000          1       5000
        20 ANALYST         6000          2       3000
        20 CLERK           1900          2        950
        20 MANAGER         2975          1       2975
        30 CLERK            950          1        950
        30 MANAGER         2850          1       2850
        30 SALESMAN        5600          4       1400
 
9 rows selected.

也可以根据多个分组进行排序

将上述结果不仅按照部门编号升序排,还要按照总工资将序排列(降序使用desc)

SQL>  select deptno 部门编号,job 岗位,sum(sal) 总工资,count(empno) 总人数, avg(sal) from emp group by deptno,job order by 部门编号,总工资 desc;
 
  部门编号 岗位          总工资     总人数        平均工资
---------- --------- ---------- ---------- ----------
        10 PRESIDENT       5000          1       5000
        10 MANAGER         2450          1       2450
        10 CLERK           1300          1       1300
        20 ANALYST         6000          2       3000
        20 MANAGER         2975          1       2975
        20 CLERK           1900          2        950
        30 SALESMAN        5600          4       1400
        30 MANAGER         2850          1       2850
        30 CLERK            950          1        950
 
9 rows selected.

 

HAVING子句

对分组后的数据进行筛选

where子句用于分组前的筛选

 

计算部门编号小于30号的部门、员工总工资、总人数、平均工资

使用where子句在分组前进行筛选

SQL>

select deptno 部门编号,sum(sal) 总工资,count(empno) 总人数, avg(sal) 平均工资

from emp

where deptno<30

group by deptno;
 
  部门编号     总工资     总人数   平均工资
---------- ---------- ---------- ----------
        20      10875          5       2175
        10       8750          3 2916.66667

使用having子句在分组后进行筛选

SQL> 

select deptno 部门编号,sum(sal) 总工资,count(empno) 总人数, avg(sal) 平均工资

from emp

group by deptno

having deptno <30;
 
  部门编号     总工资     总人数   平均工资
---------- ---------- ---------- ----------
        20      10875          5       2175
        10       8750          3 2916.66667

当然也可以在同时使用where和having子句分别对分组前及分组后的结果进行分别筛选

计算部门编号小于30且总工资小于10000的部门、员工总工资、总人数、平均工资

SQL> select deptno 部门编号,sum(sal) 总工资,count(empno) 总人数, avg(sal) 平均工资 from emp where deptno<30 group by deptno having 总工资<10000;
select deptno 部门编号,sum(sal) 总工资,count(empno) 总人数, avg(sal) 平均工资 from emp where deptno<30 group by deptno having 总工资<10000
                                                                                                                              *
ERROR at line 1:
ORA-00904: "总工资": invalid identifier

报错了…原来分组的别名不能用在having子句中

SQL>

select deptno 部门编号,sum(sal) 总工资,count(empno) 总人数, avg(sal) 平均工资

from emp

where deptno<30

group by deptno

having sum(sal)<10000;
 
  部门编号     总工资     总人数   平均工资
---------- ---------- ---------- ----------
        10       8750          3 2916.66667

这样就没有问题了。

当然如果在where子句中使用 sum(sal)<10000的话就会报错了，因为where子句中不能使用分组函数。

SQL>

select deptno 部门编号,sum(sal) 总工资,count(empno) 总人数, avg(sal) 平均工资

from emp

where sum(sal)<10000

group by deptno

having sum(sal)<10000;
select deptno 部门编号,sum(sal) 总工资,count(empno) 总人数, avg(sal) 平均工资 from emp where sum(sal)<10000 group by deptno having  sum(sal)<10000
                                                                                             *
ERROR at line 1:
ORA-00934: group function is not allowed here

 

SQL语句的编译顺序

from>where>group by>having>select>order by

 

函数嵌套

求出平均工资

SQL>

select dname from dept

where deptno=

(

select deptno from

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

where msal=(

          select max(avg(sal)) from emp group by deptno

                       )

);
 
DNAME
--------------
ACCOUNTING

最大的部门名称

也可以用rownum来查询

SQL>

select a.dname from

dept a,(

    select * from (select avg(sal) s, deptno

    from emp

    group by deptno

    order by avg(sal) desc)

    where rownum = 1) b

where a.deptno = b.deptno;
 
DNAME
--------------
ACCOUNTING

 

特例

having子句可以写在group by之后，也可以写在group by之前

SQL> select deptno,sum(sal),count(*),avg(sal) from emp having avg(sal)<2500 group by deptno;
 
 DEPTNO   SUM(SAL)   COUNT(*)   AVG(SAL)
------- ---------- ---------- ----------
     30       9400          6 1566.66667
     20      10875          5       2175
 
SQL> select deptno,sum(sal),count(*),avg(sal) from emp group by deptno having avg(sal)<2500;
 
 DEPTNO   SUM(SAL)   COUNT(*)   AVG(SAL)
------- ---------- ---------- ----------
     30       9400          6 1566.66667
     20      10875          5       2175

Aluphami
关注
  • 2
    点赞
  • 踩
  • 12
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 2
    评论
专栏目录
SQL中的聚合函数(AVG、COUNT、MAX、MIN、SUM)和数据分组
海蒂
04-07 4143
具体理解：WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。我们经常发现GROUP BY分组后的数据可能不是我们想要的，比如升序、降序，这个时候就需要再使用ORDER BY来针对分组后的数据进行排序。定义：使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值。分别取出表table_name中行column_name去重的平均值，结果名为cn_avg；SQL 中常用 5 类的函数：字符串函数，日期函数，数学函数，系统函数，聚合函数；
SQL中的5种聚集函数——均值、和、最大值、最小值、计数
weixin_37538370的博客
05-23 1699
SQL提供5种聚集函数进行数据汇总，分别是求均值avg()、sum()、max()、min()、count()。下面分别演示一下： -- 利用SQL聚集函数汇总数据 -- 均值 select avg(item_price) as avg_item_price from orderitems where order_num = 20007; -- 求和 select sum(item_price*quantity) as sum_item_price from orderitems where order_
如何使用 SQL AVG、COUNT、MAX、MIN 和 SUM 汇总数据
Vin_c° 的专栏
05-12 1157
文章目录一、聚集函数1.1 AVG() 函数1.2 COUNT() 函数1.3 MAX() 函数1.4 MIN() 函数1.5 SUM() 函数二、聚集不同值三、组合聚集函数四、小结 本文介绍什么是 SQL 的聚集函数，如何利用它们汇总表的数据。这些函数很高效，它们返回结果一般比你在自己的客户端应用程序中计算要快得多。 一、聚集函数 我们经常需要汇总数据而不用把它们实际检索出来，为此 SQL 提供了专门的函数。使用这些函数，SQL 查询可用于检索数据，以便分析和报表生成。这种类型的检索例子有： 确定表中行
SqlSugar函数查询
weixin_58342262的博客
11-15 3684
SqlSugar
Oracle函数及常用sql
01-16
- 使用聚合函数：`GROUP BY`与`SUM()`, `COUNT()`, `AVG()`, `MAX()`, `MIN()`结合。 3. **联接查询（JOIN）**： - 内连接：`INNER JOIN`，匹配两个表中的记录。 - 左连接：`LEFT JOIN`，返回左表所有记录，即使...
SQL常用函数大全
10-09
在SQL（Structured Query Language）中，函数是执行特定计算或操作的重要工具，广泛应用于数据查询、分析和处理。SQLServer2000虽然已经...无论是在简单的数据查询还是复杂的业务逻辑中，SQL函数都起着不可忽视的作用。
SQLServer中的常用函数.doc
03-21
- `MIN()` 和 `MAX()`：找出一组值中的最小值和最大值。 - `SUM()`：计算一组数值的总和。 - `COUNT()`：统计行数，`COUNT(*)`统计所有行，`COUNT(column)`统计指定列非NULL的行数。 3. **分组与排序**： - ...
sql常用函数
09-02
聚合函数用于将多个值合并为一个值，例如 COUNT、SUM、MIN 和 MAX。配置函数是一种标量函数，可返回有关配置设置的信息。转换函数将值从一种数据类型转换为另一种。加密函数支持加密、解密、数字签名和数字签名验证...
SQL常用函数
01-13
除此之外，SQL Server还提供了许多其他有用的函数，如聚合函数（如COUNT、SUM、AVG、MAX、MIN）用于对数据进行统计，字符串函数（如LEN、REPLACE、SUBSTRING）用于处理文本，日期时间函数（如GETDATE、DATEADD、...
数据库2字段统计平均值、AS、avg、round的结合用法
qq_45876815的博客
03-02 2213
数据库2字段统计平均值，as、avg、sum、round用法
sql学习 sum()和count()
最新发布
ferventtempo_的博客
03-03 919
注意：count（条件）即使条件不满足，括号内为非null时也会加1，而sum(条件)只有在满足条件表达式才加1。count(distinct column_name) 函数返回column_name不同的的行数。将null改成0，结果为2，count将declined结果也计入了。count(column_name) 函数返回指定列的值的数目。count(*) 、count（1）函数返回表的行数。使用if(a,b,c)，当a成立时返回b，否则返回c。sum() 函数返回数值列的总数。
Sql根据不同条件统计总数
热门推荐
SunnyRivers
10-29 3万+
前言 经常会遇到根据不同的条件统计总数的问题，一般有两种写法：count和sum都可以 数据准备： 方法一 ：Count 代码： SELECT COUNT( CASE WHEN age > 20 AND age < 25 THEN 1 ELSE NULL END ) AS cnt0, COUNT( CASE WHEN age >= ...
sql窗口函数之聚合窗口函数(count,max,min,sum)
qq_43314839的博客
02-09 2528
题1 https://www.nowcoder.com/practice/2b7acdc7d1b9435bac377c1dcb3085d6?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0 SELECT uid, exam_id, ROUND(avg(if(score_cnt>1,(score-min_score)/(max_score-min_score)*100,score)),0) avg_
聚合查询的用法（当有sum/avg）这些函数时的用法
蒗若晨曦
12-04 2650
当一个语句中有sum/ave这样的函数时，在库中进行查询时，容易出现“ORA-00937: 非单组分组函数”这样的错误，这是因为在聚合查询列中，有没有聚合的列：如 Select 井号,TO_CHAR(生产日期,'yyyy'),TO_CHAR(生产日期,'mm'),sum(生产时间),avg(yy),avg(yt),avg(jt),avg(bt),sum(cql),avg(ly),avg(jy),
在oracle的聚合函数(sum、avg等)中使用group by来分组你的结果
☂yǒng 超-站在巨人的肩膀上来创新.
08-09 5389
前面我们介绍了几个oralce中聚合函数(count、max、min、sum、avg等)，但实际上在oracle中，单独使用这几个聚合函数的机会并不多，通常是和group by 关键字结合使用的。 Group by的含义是对结果进行分组。结合具体的例子来说好了，比如要计算工资的平均值，我们可以对每个部门进行分别求平均值，这样就能看出研发部门和销售部门待遇的差距了，呵呵！ 要按部门求得平
SQL中AVG()、COUNT()、SUM()等函数对NULL值处理
jarniyy的博客
06-25 2万+
原文链接：https://blog.csdn.net/whaxrl/article/details/51195241一、AVG()求平均值注意AVE()忽略NULL值，而不是将其作为“0”参与计算二、COUNT()两种用法1、COUNT(*)对表中行数进行计数不管是否有NULL2、COUNT(字段名)对特定列有数据的行进行计数忽略NULL值三、SUM()可以对单个列求和，也可以对多个列...
小白成长记（三、SqlSugar的sum用法）
啊鹏的博客
01-13 5152
SqlSugar的GroupBy和Sum用法 hello,大家好。我是闲齁齁，一枚用发量写代码的程序员。今天我做的功能模块里面涉及到了分组和求和这两个功能，于是我就翻阅了SqlSugar的文档来学习分组查询和使用。但是还是写出来了好多问题，接下来我就把问题和解决方法写出来，来帮小伙伴们避个雷。SqlSugar的官方文档我已经在下面打了出来。 http://www.donet5.com/Home/Doc?typeId=2243 首先按我以往的规矩来，先写接口，接口的代码还是和以前一样，不知道怎么写的小伙伴
3. 聚合函数【sum、avg、min、max、count、count（*）】+ 分组查询【group by 和 having】 + 查询结果排序【order by】
你说的白是什么白_
03-22 314
文章目录聚合函数分组查询 聚合函数 可以实现数据集合的汇总或是求平均值等各种运算。 查询学号为‘1’的学生平均成绩。 SELECT avg(score) AS 平均成绩 FROM sc WHERE sno= '1' 查询学生的人数。 SELECT count(*) AS 学生人数 FROM student 查询专业的种类（相同的按一种计算）。 SELECT count (DISTINCT specialty) AS 专业种类数 FROM student 分组查询 查询“teaching
sql 常用函数 统计函数
u011277123的博客
03-17 3102
SQL与js的实验室 2017-02-03 18:29 入门级的统计函数： （1）统计记录数 count函数 可以统计出这条sql会查询出多少条记录很多人用count(*),但建议用count(1) （2）求和函数 sum select SUM(num) from tmp_p 对表中 num列进行求和 （3）求平均值 select AVG(num) from t
SQL SEVER使用集函数(count、sum、avg、max、min)
05-29
常用的聚合函数包括 COUNT、SUM、AVG、MAX 和 MIN 等。 以下是各个聚合函数的使用方法： 1. COUNT：用于计算指定列中的行数，语法如下： ``` SELECT COUNT(column_name) FROM table_name; ``` 例如，统计一个...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Aluphami

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值