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

最新推荐文章于 2023-04-07 23:28:48 发布
最新推荐文章于 2023-04-07 23:28:48 发布
阅读量4.6k 收藏 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
    评论
专栏目录
Oracle函数及常用sql
01-16
- 使用聚合函数：`GROUP BY`与`SUM()`, `COUNT()`, `AVG()`, `MAX()`, `MIN()`结合。 3. **联接查询（JOIN）**： - 内连接：`INNER JOIN`，匹配两个表中的记录。 - 左连接：`LEFT JOIN`，返回左表所有记录，即使...
SQL AVG函数
最新发布
QYgujingjing的专栏
04-10 5370
SQL AVG函数
T-SQL中的聚合函数、SUM()、AVG()、MAX() 函数和 MIN() 函数的使用
yiyayiya_w的博客
12-15 5910
T-SQL中的聚合函数 1.SUM() 函数 SUM() 函数返回表达式中所有数值的总和，空值将被忽略。SUM() 函数只能用于数字类型的列，不能够汇总字符，日期等其他数据类型。例如，查询学生编号为S2102001的考试总分，可以使用如下查询。 SudentResult是我的学生成绩 SELECT SUM(SudentResult) AS 学号为S2102001的学生总分 FROM Result WHERE StudentNo = 'S2102001'; 得到查询结果如下图： 注意这种查询只返回一个数
Day458.聚合函数 -mysql
阿昌爱Java
11-23 4085
聚合函数 聚合（或聚集、分组）函数，它是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。 一、聚合函数介绍 什么是聚合函数 聚合函数作用于一组数据，并对一组数据返回一个值。 聚合函数类型 AVG() SUM() MAX() MIN() **COUNT() ** 聚合函数语法 聚合函数不能嵌套调用。比如不能出现类似“AVG(SUM(字段名称))”形式的调用。 1、AVG和SUM函数 可以对数值型数据使用AVG 和 SUM 函数。 SELECT AVG(salary
基础篇8、聚合函数
希望有点用
06-21 728
聚合函数
SQL中的聚合函数(AVG、COUNT、MAX、MIN、SUM)和数据分组
海蒂
04-07 4178
具体理解：WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。我们经常发现GROUP BY分组后的数据可能不是我们想要的，比如升序、降序，这个时候就需要再使用ORDER BY来针对分组后的数据进行排序。定义：使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值。分别取出表table_name中行column_name去重的平均值，结果名为cn_avg；SQL 中常用 5 类的函数：字符串函数，日期函数，数学函数，系统函数，聚合函数；
SQL Server中的常用函数汇总
12-14
4. **`Max()`** 和 **`Min()`**：找出一组值的最大值和最小值。 5. **`GroupBy()`** 和 **`Having()`**：通常与聚合函数一起使用，用于分组数据并根据条件筛选分组。 以上只是SQL Server中函数的一部分，实际上还有...
SQL常用函数大全
10-09
在SQL（Structured Query Language）中，函数是执行特定计算或操作的重要工具，广泛应用于数据查询、分析和处理。SQLServer2000虽然已经...无论是在简单的数据查询还是复杂的业务逻辑中，SQL函数都起着不可忽视的作用。
sql数据库基础知识整理,常用函数及常用语法
12-23
`MAX()`、`MIN()`用于找出列中的最大和最小值；`SUM()`计算一列数值的总和；`AVG()`则返回平均值；而`COUNT()`则统计行数。这些函数常用于数据分析和报表制作。 接着是字符串处理函数。`LEN()`返回字符串的字符长度...
SQLServer中的常用函数.doc
03-21
- `MIN()` 和 `MAX()`：找出一组值中的最小值和最大值。 - `SUM()`：计算一组数值的总和。 - `COUNT()`：统计行数，`COUNT(*)`统计所有行，`COUNT(column)`统计指定列非NULL的行数。 3. **分组与排序**： - ...
如何使用 SQL AVG、COUNT、MAX、MIN 和 SUM 汇总数据
Vin_c° 的专栏
05-12 1186
文章目录一、聚集函数1.1 AVG() 函数1.2 COUNT() 函数1.3 MAX() 函数1.4 MIN() 函数1.5 SUM() 函数二、聚集不同值三、组合聚集函数四、小结 本文介绍什么是 SQL 的聚集函数，如何利用它们汇总表的数据。这些函数很高效，它们返回结果一般比你在自己的客户端应用程序中计算要快得多。 一、聚集函数 我们经常需要汇总数据而不用把它们实际检索出来，为此 SQL 提供了专门的函数。使用这些函数，SQL 查询可用于检索数据，以便分析和报表生成。这种类型的检索例子有： 确定表中行
sql 语句 MAX()+SUM() 使用
lesiyu116的博客
05-27 4903
支行信息branch(branch_name,branch_city) 账户信息 account(account_name,branch_name,balance) 查询 存款最多的支行 1.取巧的方法 SELECT SUM(a.balance),a.branch_name FROM account a,branch b WHERE a.branch_name=b.branch_name...
SQL中的5种聚集函数——均值、和、最大值、最小值、计数
weixin_37538370的博客
05-23 1704
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复习
imlpf的专栏
03-31 473
select * from emp conn sys/change_on_install as sysdba--sys 必须加上 as sysdba show user conn system/manager show user set linesize 300 set pagesize 30 select * from scott.emp--sys用户下访问emp select * from d
常用的sql查询语句
qq_48596411的博客
06-27 425
一、简单查询语句 1. 查看表结构 SQL>DESC emp; 2. 查询所有列 SQL>SELECT * FROM emp; 3. 查询指定列 SQL>SELECT empmo, ename, mgr FROM emp; SQL>SELECT DISTINCT mgr FROM emp; 只显示结果不同的项 4. 查询指定行 SQL>SELECT * FROM emp WHERE job='CLERK'; 5. 使用算术表达式 SQL>SELECT
SqlSugar函数查询
weixin_58342262的博客
11-15 3734
SqlSugar
sql sum()与avg() 函数
qq_42833608的博客
10-14 1891
AVG() 函数返回数字列的平均值 注意是数字的平均数， 语法： select avg(字段) from 表名 建个表，弄点数据 求工资平均数 select avg(字段) as 平均数 from 表名 与where 字句一块使用 与count 函数 sum（） 求和函数 返回数字列的总和 语法： select sum(字段) as 总和 from 表名 与avg 函数一块使用 ...
数据库2字段统计平均值、AS、avg、round的结合用法
qq_45876815的博客
03-02 2238
数据库2字段统计平均值，as、avg、sum、round用法
SQL函数之聚合函数（求和，平均值，最大值，最小值，统计，取不重，取重）
热门推荐
皮卡丘丘丘~~~
02-22 4万+
聚合函数 聚合函数对一组值进行计算并返回单一的值，通常聚合函数会与SELECT语句的GROUP BY子句一同使用，在与GROUP BY子句使用时，聚合函数会为每一个组产生一个单一值，而不会为整个表产生一个单一值. 在这张数据表的基础上执行语句 SUM(求和)函数 SUM函数返回表达式中所有值的和或仅非重复值的和。SUM 只能用于数字列。空值将被忽略。 参数说明： ALL：对所有的值应用此聚合函数...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

打赏作者

Aluphami

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

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

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

打赏作者

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

抵扣说明：

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

余额充值