Sql server 中count() 与 sum() 的区别

r囧r小猫 2021-10-29 12:18 355阅读 0赞

一句话概括就是Sum(列) 是求和，把所有列的值进行汇总求和；  
COUNT(列) 是行数汇总，只要列的值不为Null，就会增加1；

举个例子说明下：

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

--创建临时表结构
    CREATE TABLE TempTB
    (
        ID int ,
        Name varchar(20),
        Price Int
    )
    --写入示例数据
    INSERT INTO TempTB values(1,'苹果',10);
    INSERT INTO TempTB values(2,null,35);
    INSERT INTO TempTB values(3,'橘子',18);
    INSERT INTO TempTB values(null,null,null);

\--查看所有数据  
SELECT \* FROM TempTB

![404126-20190803180112637-1944354936.png][]

看下面汇总结果：  
SELECT COUNT(Price) as '总行数',SUM(Price) as '求和' FROM TempTB

![404126-20190803180317212-1982888151.png][]

在使用Count(列) 的时候，需要注意，只有当列不为Null的时候，行数才会加1；否则汇总行数，上面的结果也可以看出，第四行的价格为Null，所以行数统计只有3；

但是有个例外，就是count(\*)，在不指定列的情况下，即使此行中所有的列都是Null值，在汇总行数时仍然会+1 ；  
比如：  
SELECT COUNT(\*),COUNT(ID) FROM TempTB

![404126-20190803180535747-478862286.png][]

**参考：**  
[https://my.oschina.net/u/3497902/blog/2208655][https_my.oschina.net_u_3497902_blog_2208655]  
[https://www.cnblogs.com/CareySon/p/DifferenceBetweenCountStarAndCount1.html\#!comments][https_www.cnblogs.com_CareySon_p_DifferenceBetweenCountStarAndCount1.html_comments]  
[https://www.cnblogs.com/yjd\_hycf\_space/p/9985393.html][https_www.cnblogs.com_yjd_hycf_space_p_9985393.html]

转载于:https://www.cnblogs.com/Rawls/p/11295703.html

[ContractedBlock.gif]: https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20211029/95f577e9d6b04c998fe64b25970e573f.png
[404126-20190803180112637-1944354936.png]: /images/20211029/f768d7f531a847bb94cd3155e13930e8.png
[404126-20190803180317212-1982888151.png]: /images/20211029/97cbc54419414fc7aadcf0faf34310d7.png
[404126-20190803180535747-478862286.png]: /images/20211029/d8027fe87cdc4802af41b2b2143677c1.png
[https_my.oschina.net_u_3497902_blog_2208655]: https://my.oschina.net/u/3497902/blog/2208655
[https_www.cnblogs.com_CareySon_p_DifferenceBetweenCountStarAndCount1.html_comments]: https://www.cnblogs.com/CareySon/p/DifferenceBetweenCountStarAndCount1.html#!comments
[https_www.cnblogs.com_yjd_hycf_space_p_9985393.html]: https://www.cnblogs.com/yjd_hycf_space/p/9985393.html