mySQL中AVG()函数如何去除null值或0值求平均值

1、当值为null时，求平均数

聚合函数会把NULL排除在外，但count（*）例外，并不会排除NULL，但count(book_price)不会排除null
在这里插入图片描述
如果对book_price这个字段计算求平均值的话，结果为30

SELECT round(avg(book_price),2) FROM `t_book`;

是因为数据库聚合的时候会删除NULL列，然后计算：(20+35+25+20+40+30+50+20)/8=30，注意分母是8不是10
在这里插入图片描述
如果需要将NULL值也作为分母来计算的话，需要使用COALESCE()函数将NULL值转换为其他值(如0)，

SELECT round(avg(coalesce(book_price,0)),2) FROM `t_book`;

则计算方式为：(20+35+25+20+40+30+50+20)/10=24
在这里插入图片描述

2.当值为0时，求平均数

在这里插入图片描述

SELECT round(avg(book_price),2) FROM `t_book`;

计算方式为：(20+35+25+20+40+30+50+20)/10=24
在这里插入图片描述
若需要将值为0的数据去掉，不做计算的话，可以使用nullif()函数

#nullif(book_price,0)------》 若book_price=0话，返回null,而不是0
SELECT round(avg(nullif(book_price,0))) FROM `t_book`;

计算方式为：(20+35+25+20+40+30+50+20)/8=30
在这里插入图片描述

3、特殊情况：当值为0时，且求平均值的结果也为0时，如下图

在这里插入图片描述
其结果为：
在这里插入图片描述
这样的查询结果若展示到前端，可能会有影响，出现undefined的情况，如下图
在这里插入图片描述
需要解决此问题的话，可以用case搜索函数，将结果为null的转换为0进行展示

select (case when round(avg(nullif(book_price,0))) is null then 0 else round(avg(nullif(book_price,0))) end) book_price FROM `t_book`;

结果为：
在这里插入图片描述