SQL聚合函数的使用总结及注意事项

在用SQL处理数值相关的列或者表的时候，会用聚合函数。

什么是聚合函数？聚合函数一般会出现在哪些语句中呢？

聚合函数：

一、定义：

SQL基本函数，聚合函数对一组值执行计算，并返回单个值，也被称为组函数。常见的聚合函数如下：

count()、sum()、avg()、min()、max()；

二、应用场景：

聚合函数可以应用于查询语句的SELECT中，或者HAVING子句中，并结合group by使用。注意：不可用于WHERE语句中，因为WHERE是对逐条的行记录进行筛选。

三、应用：

1、count()：求表的行数或者指定表中某个列的列值行数，null值被忽略。

     select   count(字段)  from  表名;    --得到该列值的非null值的行数

     select   count(*)   from   表名;     --用于统计整个表的行数。任何行，只要有一行非null，则整个表的行数就会被统计上。全为null（不会出现该情况）则不被统计

2、sum()：返回指定数据的和，只能用于数字列，null值被忽略。

     select  sum(salary)   from  emp;   --求所有salary的总和

3、max()：返回一列中的最大值，null值被忽略。

      select   max(column_name)    from   table_name;

 4、min()：返回一列中的最小值，null值被忽略。

      select   max(column_name)    from   table_name;

5、avg()：返回数值列的平均值，，null值被忽略。

     select   avg(salary)   as   avg_sal  from   emp;

四、关于null值的讨论

1、count()：

      当把列名称作为参数时，count函数会忽略null。使用符号*或者常量参数的时候，就会包含null。请看下面示例

select depno, comm
  from emp;
/*结果
depno         comm
10
10
10
15
15
15
20            300
20
20
20            100
20
30
30
30
*/
select count(*),
       count(depno),
       count(comm),
       count('hello')
  from emp;
/*结果
count(*)      count(depno)        count(comm)        count('hello')
  14              14                  2                  14           
*/
select depno,
       count(*),
       count(comm),
       count('hello')
  from emp
 group by depno;
/*结果
depno         count(*)      count(comm)      count('hello')
  10             3             0                  3
  15             3             0                  3
  20             5             2                  5
  30             3             0                  3
*/
--如果参数列的值都为null，或者表里没有任何数据，count函数就会返回0

2、sum()：

             sum函数会忽略null，但我们可能会遇到null分组。depno为10的员工都没有业务提成，因而对于depno为10的分组而言，计算comm的总和会返回null。请看下面示例

select depno,
       sum(comm)
  from emp
 where depno in (10,20)
 group by depno;
/*结果：
depno       sum(comm)
10
20          400
*/

3、max() 与min()：

             min函数与max函数会忽略Null，而我们可能会遇到null分组，也可能会在一个分组里遇到null值。在下面的查询中，group by查询的结果里会有两个分组返回null值。请看下面示例

select depno,                     select min(comm),                 select depno,
       comm                              max(comm)                         min(comm),
  from emp                          from emp;                              max(comm)
 where depno in (10,20)                                               from emp
 order by 1;                                                         group by depno;
/*结果
depno     comm                   min(comm)      max(comm)          depno   min(comm) max(comm) 
10                               100            300	                10       
10	                                                                20      100       300
10	                                                                15
20	                                                                30
20	      
20	      
20	      100
20	      300
*/

4、avg()：

             针对整个表计算平均值，只要对所要求的列使用avg函数即可；请记住：avg会忽略null！！！当使用聚合函数的时候，一定要先想一下如何处理null！！！！请看下面示例

create table t2(sal integer);

insert into t2 values(10);
commit;

insert into t2 values(20);

commit;
insert into t2 values(null);
commit;

select avg(sal)                      select distinct 30/2
  from t2;                             from t2;

     avg(sal)                                30/2  
  ----------                             ------------
        15                                    15    


select avg(coalesce(sal,0))          select distinct 30/3
  from t2;                             from t2;

 avg(coalesce(sal,0))                        30/3
 -----------------------              ----------------
        10                                    10

coalesce(sal,0)：coalesce函数是将sal为空的值替换为0

涉及null值的计算，可以用coalesce函数将null值替换为指定的值，再计算。

其他：

1、having是一个过滤声明，通常在查询书里末端，主要用来对经过前面各种约束后查询到的数据结果进行过滤。注意：having只能放在group by之后并结合group by使用，没有group by 不能使用having。

2、group by子句只能放非聚合函数的列。

3、where是一个约束声明，在较早的位置执行，先执行where的约束条件我，再返回结果。注意：where子句不能使用聚合函数。