sql中的count与sum的辨析

我们写的SQL, 都是整行数据操作的, 如果要操作某列的数据, 就需要使用到 聚合函数了

count与sum都是聚合函数：
        count()     统计数据表 总数据条数.
        sum()       求某列值的: 和

而在某种情况下，count与sum都能够用来计数，但带来信息十分具有迷惑性，非常有趣，请看以下例子。

-- 需求: 输出报表，统计不同供应商供应商品的总库存量，以及高价值商品的种类个数（单价超过40定义为高价值）
-- 结果显示四列：
-- 供应商ID supplier_id
-- 供应商公司名 company_name
-- 由该供应商提供的总库存 all_units
-- 由该供应商提供的高价值商品库存的种类个数 expensive_units

使用sum计数高价值商品的种类个数

select p.supplier_id,company_name,sum(units_in_stock) all_units,
       sum(if(unit_price > 40, 1,0)) as expensive_units
       from products p join suppliers s on p.supplier_id = s.supplier_id group by p.supplier_id;

 将产品表与供应商表以supplier_id（供应商编号）作为关联条件进行内连接，并按照supplier_id进行分组。如果存在一种商品的unit_price（单价）> 40，则记1，否则记为0，以此累加。

结果如图

以第一行名为“Exotic Liquids”的公司为例，supplier_id = 1 , 可以明显看出expensive_units=0。说明此公司供应的所有商品的unit_price(单价)<40。

使用count计数高价值商品的种类个数

select p.supplier_id,company_name,sum(units_in_stock) all_units,
       count(if(unit_price > 40, 1,0)) as expensive_units
       from products p join suppliers s on p.supplier_id = s.supplier_id group by p.supplier_id;

 其他条件没有任何改变，只将sum改成了count。

如果存在一种商品的unit_price（单价）> 40，则记1，否则记为0，以此计数。

结果如图

 还是以这个名为“Exotic Liquids”的公司为例，supplier_id = 1 , 就会发现一个很神奇的情况。

为什么这次，expensive_units=3？

这个3是从哪来的呢？这个名为“Exotic Liquids”的公司，供应的商品的价格到底有没有>40呢？

这时候相信很多读者看到这，都想看一下供应商品价格>40的信息，到底是什么情况。那么咱们就来验证一下。

select supplier_id,unit_price,units_in_stock from products where unit_price>40;

结果如图 

查看了所有unit_price>40的信息，可以看出supplier_id(供应商id)并没有出现1，也就是名为“Exotic Liquids”的公司根本就没有供应unit_price>40的商品。那么他到底供应了什么样的商品呢？

select supplier_id,unit_price,units_in_stock from products where products.supplier_id = 1 ;

结果如图

明明 “Exotic Liquids”的unit_price分别为18，19，10，那么为什么采取count计数会出现3，而用sum计数还是0呢？

总结

我们忽略了count与sum的一个区别

sum(if(unit_price > 40, 1,0)) as expensive_units

count(if(unit_price > 40, 1,0)) as expensive_units

当利用sum时，只要出现满足条件的值我们就记为1，没有则记为0，依次累加。

当利用count时，只要出现满足条件的值我们就记为1，没有则记为0，依次累加。

但是

事实上，并非如此

利用count进行计数，满足条件则记为1

当不满足条件时，会记为0

而count会统计所有满足条件的值的次数    以及     非null的次数

采用sum则不会有这个问题

满足为1

不满足为0

最终加总，该是多少就是多少

所以想要呈现出与sum一样的效果，只需要将count(if(unit_price > 40, 1,0)) as expensive_unit改为

count(if(unit_price > 40, 1,null))

拓展

面试题: count(*), count(1), count(列)的区别?

区别1: 统计范围不同.
                count(列):            只统计该列的 非null值.
                count(*), count(1):   无论是否为null, 都统计.
区别2: 效率不同.
                从高到低分别是: count(主键列) > count(1) > count(*) > count(普通列)