sql查询中sum和count之间的差异 [英] Difference between sum and count in sql query

问题描述

Hello Experts，



我在网上搜索上述问题时遇到过这个网站。

http://www.w3resource.com/sql/aggregate-functions/sum-and-count- using-variable.php [ ^ ]



网站上的第一句话说，

要获得'customer'表中记录总数的总和，可以使用以下SQL语句

为什么我们不能使用从Tablename中选择count（*）来获取记录总数？

基本上我没有在url中这个特定示例的区别。有人可以帮我这个吗？



或者有人可以告诉我我们在下面写一个查询有多么不同但它应该为这两个查询返回相同的结果集

 选择 icitem，icdsc1，icdsc2，sum（ifqoh） as  onhand，sum（ifqcm） as   commit  来自 TESTDATA.VINITMB  join  TESTDATA.VINITEM  on  ifcomp = 1 和 ifitem = icitem 和 ifdel = '  A' 
 其中 icitem 喜欢 '  VP％'  group  < span class =code-keyword> by  icitem，ic dsc1，icdsc2 
   sum（ifqoh）= 0  order   by  icitem; 

解决方案

这很简单：



你可以算什么。你甚至可以确保它的区别如 Count（不同状态）



你只能加一个数值。 sum将每一行加在一起。



如果你的列有1,1,1,1,2,2，那么：

 Count（col）=  6  
 Count（ Distinct  col）=  2  
 Sum（col）=  8  

希望有所帮助



Andy ^ _ ^

Hello Experts,

I came across this website during my online search for the above question.
http://www.w3resource.com/sql/aggregate-functions/sum-and-count-using-variable.php[^]

The very first statement in the website says,

To get sum of total number of records in 'customer' table, the following SQL statement can be used

Why can't we use select count(*) from Tablename to get the total number of records?
Basically I didn't the difference of this particular example in the url. Can someone help me with this?

Or can someone tell me how different can we write a query for below but it should return same resultset for both the queries

select icitem,icdsc1,icdsc2, sum(ifqoh) as onhand, sum(ifqcm) as commit from TESTDATA.VINITMB join TESTDATA.VINITEM on ifcomp=1 and ifitem=icitem and ifdel='A'
where icitem like 'VP%' group by icitem, icdsc1, icdsc2
having sum(ifqoh)=0 order by icitem;

解决方案

It's easy:

You can count anything. You can even ensure its distinct like Count(distinct status)

you can only sum a numerical value. sum adds each row together.

If you had a column with 1,1,1,1,2,2, then:

Count(col) = 6
Count(Distinct col) = 2
Sum(col) = 8

Hope that helps

Andy ^_^

这篇关于sql查询中sum和count之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！