What's the differences among Count(0), Count(1) and Count(SomeField)http://forums.asp.net/t/1803267.aspx/1?What+s+the+differences+among+Count+0+Count+1+and+Count+SomeField+Tue, 15 May 2012 09:12:31 -040018032674978686http://forums.asp.net/p/1803267/4978686.aspx/1?What+s+the+differences+among+Count+0+Count+1+and+Count+SomeField+What's the differences among Count(0), Count(1) and Count(SomeField) <p>Hi all</p> <p>Several questions want to know</p> <p>What's <strong>select count(0)</strong> in MSSQL</p> <p>What's&nbsp;<strong>select count(1)</strong>&nbsp;in MSSQL</p> <p>What's&nbsp;<strong>select count(FieldName)</strong>&nbsp;in MSSQL</p> <p>How about their speedAnd if FieldName is a primary keywhy the speed is the slowestI don't know why</p> <p>Reguards</p> 2012-05-14T01:34:40-04:004978701http://forums.asp.net/p/1803267/4978701.aspx/1?Re+What+s+the+differences+among+Count+0+Count+1+and+Count+SomeField+Re: What's the differences among Count(0), Count(1) and Count(SomeField) <p>Well,</p> <p>Count(1) will run faster than Count(*).</p> <p>Also Count(1) will return the number of rows and not the data.</p> <p>count(FieldName) will only count table where fiedName is not null.</p> <p>This might help you&nbsp;<a href="http://www.techonthenet.com/sql/count.php">http://www.techonthenet.com/sql/count.php</a></p> 2012-05-14T02:21:32-04:004978718http://forums.asp.net/p/1803267/4978718.aspx/1?Re+What+s+the+differences+among+Count+0+Count+1+and+Count+SomeField+Re: What's the differences among Count(0), Count(1) and Count(SomeField) <p>Count(*)/Count(0)/Count(1) - All are same and it gives the number of rows in the result set. &nbsp;But if you specify the column name, it gives the number of rows in the result set where the specified column is not null.</p> 2012-05-14T02:54:35-04:004978890http://forums.asp.net/p/1803267/4978890.aspx/1?Re+What+s+the+differences+among+Count+0+Count+1+and+Count+SomeField+Re: What's the differences among Count(0), Count(1) and Count(SomeField) <p>Hey all again</p> <p>Why count(0) and count(1)What does 0 and 1 mean hereAnd what's the MOST DIFFERENCE BETWEEN the two statements</p> 2012-05-14T05:11:59-04:004979137http://forums.asp.net/p/1803267/4979137.aspx/1?Re+What+s+the+differences+among+Count+0+Count+1+and+Count+SomeField+Re: What's the differences among Count(0), Count(1) and Count(SomeField) <p></p> <blockquote><span class="icon-blockquote"></span> <h4>TimoYang</h4> <p></p> <p>Hey all again</p> <p>Why count(0) and count(1)What does 0 and 1 mean hereAnd what's the MOST DIFFERENCE BETWEEN the two statements</p> <p></p> </blockquote> <p></p> <p>There is no difference.</p> <p>you must see this</p> <pre class="prettyprint">select count(0) --0 means expression only select count(1) --1 means expression only ---both above results show output one --becuase count function require --as well as check this select COUNT(*) --this will also return value 1 becuase count function is not used in select query</pre> <p><br> <br> </p> 2012-05-14T07:19:34-04:004979158http://forums.asp.net/p/1803267/4979158.aspx/1?Re+What+s+the+differences+among+Count+0+Count+1+and+Count+SomeField+Re: What's the differences among Count(0), Count(1) and Count(SomeField) <p></p> <blockquote><span class="icon-blockquote"></span> <h4>TimoYang</h4> Why count(0) and count(1)What does 0 and 1 mean hereAnd what's the MOST DIFFERENCE BETWEEN the two statements</blockquote> <p></p> <p>I would suggest you to check out below links, which will clear you doubts :</p> <p><a href="http://blog.sqlauthority.com/2008/02/26/sql-server-select-1-vs-select-an-interesting-observation/" target="_blank">http://blog.sqlauthority.com/2008/02/26/sql-server-select-1-vs-select-an-interesting-observation/</a></p> <p><a href="http://beyondrelational.com/modules/2/blogs/116/posts/12000/sql-server-count-or-count1-which-is-better.aspx" target="_blank">http://beyondrelational.com/modules/2/blogs/116/posts/12000/sql-server-count-or-count1-which-is-better.aspx</a></p> <p>&nbsp;</p> <p>&nbsp;</p> 2012-05-14T07:25:25-04:004979205http://forums.asp.net/p/1803267/4979205.aspx/1?Re+What+s+the+differences+among+Count+0+Count+1+and+Count+SomeField+Re: What's the differences among Count(0), Count(1) and Count(SomeField) <p>Hi, I believe count(0) is counting the first column, Count(1) would be the second column in the table.&nbsp;</p> <p>Since your specifying the column here, it will only count the non-null records in the specified column.&nbsp; Count(*) will give you a true count of the rows in the table.</p> 2012-05-14T08:03:09-04:004979247http://forums.asp.net/p/1803267/4979247.aspx/1?Re+What+s+the+differences+among+Count+0+Count+1+and+Count+SomeField+Re: What's the differences among Count(0), Count(1) and Count(SomeField) <p></p> <blockquote><span class="icon-blockquote"></span> <h4>christiandev</h4> Hi, I believe count(0) is counting the first column, Count(1) would be the second column in the table.&nbsp;</blockquote> <p></p> <p>No. COUNT(0) &amp; COUNT(1) does not specify column name at all.</p> <p>0,1 &amp; any thing inside are only expression say value..</p> <p>SELECT COUNT(0) --return 1</p> <p>SELECT COUNT(1) --return 1</p> <p>SELECT COUNT(100) --return 1</p> <p>SELECT COUNT(*) --return 1</p> 2012-05-14T08:19:00-04:004979280http://forums.asp.net/p/1803267/4979280.aspx/1?Re+What+s+the+differences+among+Count+0+Count+1+and+Count+SomeField+Re: What's the differences among Count(0), Count(1) and Count(SomeField) <p></p> <blockquote><span class="icon-blockquote"></span> <h4>yrb.yogi</h4> <p></p> <p>No. COUNT(0) &amp; COUNT(1) does not specify column name at all.</p> <p>0,1 &amp; any thing inside are only expression say value..</p> <p></p> </blockquote> <p></p> <p>Very True.&nbsp;</p> 2012-05-14T08:33:34-04:004979282http://forums.asp.net/p/1803267/4979282.aspx/1?Re+What+s+the+differences+among+Count+0+Count+1+and+Count+SomeField+Re: What's the differences among Count(0), Count(1) and Count(SomeField) <p></p> <blockquote><span class="icon-blockquote"></span> <h4>yrb.yogi</h4> <p></p> <p></p> <blockquote><span class="icon-blockquote"></span> <h4>christiandev</h4> Hi, I believe count(0) is counting the first column, Count(1) would be the second column in the table.&nbsp;</blockquote> <p></p> <p>No. COUNT(0) &amp; COUNT(1) does not specify column name at all.</p> <p>0,1 &amp; any thing inside are only expression say value..</p> <p>SELECT COUNT(0) --return 1</p> <p>SELECT COUNT(1) --return 1</p> <p>SELECT COUNT(100) --return 1</p> <p>SELECT COUNT(*) --return 1</p> <p></p> </blockquote> <p></p> <p>I always use Count(*), and I was under the impression that the value was the ordinal position - learn something every day :) - the rest is still correct though, count(*) will return even when null, count(column_name) will only return non-null in the count?</p> 2012-05-14T08:34:58-04:004980691http://forums.asp.net/p/1803267/4980691.aspx/1?Re+What+s+the+differences+among+Count+0+Count+1+and+Count+SomeField+Re: What's the differences among Count(0), Count(1) and Count(SomeField) <p></p> <blockquote><span class="icon-blockquote"></span> <h4>christiandev</h4> Hi, I believe count(0) is counting the first column, Count(1) would be the second column in the table.&nbsp;</blockquote> <p></p> <p>If soWhy can I also do select count(1000)My table only has two columnsIt should be wrong</p> 2012-05-15T02:30:41-04:004980693http://forums.asp.net/p/1803267/4980693.aspx/1?Re+What+s+the+differences+among+Count+0+Count+1+and+Count+SomeField+Re: What's the differences among Count(0), Count(1) and Count(SomeField) <p></p> <blockquote><span class="icon-blockquote"></span> <h4>yrb.yogi</h4> There is no difference.</blockquote> <p></p> <p>WhyDon't you think Microsoft is so &quot;stupid&quot; to develop such a duplicated functionhahahaI'm afraid it's not the reason</p> 2012-05-15T02:31:45-04:004980694http://forums.asp.net/p/1803267/4980694.aspx/1?Re+What+s+the+differences+among+Count+0+Count+1+and+Count+SomeField+Re: What's the differences among Count(0), Count(1) and Count(SomeField) <p><span>How about their speed between&nbsp;<strong>select count(0)</strong><span>&nbsp;<strong>select count(1) and&nbsp;<strong>select count(FieldName)Why</strong></strong></span></span></p> 2012-05-15T02:33:24-04:004980699http://forums.asp.net/p/1803267/4980699.aspx/1?Re+What+s+the+differences+among+Count+0+Count+1+and+Count+SomeField+Re: What's the differences among Count(0), Count(1) and Count(SomeField) <p></p> <blockquote><span class="icon-blockquote"></span> <h4>HarryBrock</h4> Count(1) will run faster than Count(*).</blockquote> <p></p> <p>I was once told by someone that Count(*) is faster than Count(1)</p> <p><img src="http://forums.asp.net/scripts/tiny_mce/plugins/emotions/img/smiley-innocent.gif" alt="Innocent" title="Innocent" border="0" class="emoticon"></p> 2012-05-15T02:36:05-04:004980708http://forums.asp.net/p/1803267/4980708.aspx/1?Re+What+s+the+differences+among+Count+0+Count+1+and+Count+SomeField+Re: What's the differences among Count(0), Count(1) and Count(SomeField) <p></p> <blockquote><span class="icon-blockquote"></span> <h4>TimoYang</h4> I was once told by someone that Count(*) is faster than Count(1)</blockquote> <p></p> <p>Did you look at the link from my first post?</p> 2012-05-15T02:44:38-04:004980821http://forums.asp.net/p/1803267/4980821.aspx/1?Re+What+s+the+differences+among+Count+0+Count+1+and+Count+SomeField+Re: What's the differences among Count(0), Count(1) and Count(SomeField) <p></p> <blockquote><span class="icon-blockquote"></span> <h4>HarryBrock</h4> Did you look at the link from my first post?</blockquote> <p></p> <p>I lookedhowever I think now that count(1) will be automatically converted to count(*)and this will take sometime</p> <p><a href="http://stackoverflow.com/questions/1221559/count-vs-count1">http://stackoverflow.com/questions/1221559/count-vs-count1</a></p> <p>NOT SURE WHO IS RIGHT</p> 2012-05-15T05:00:47-04:004980909http://forums.asp.net/p/1803267/4980909.aspx/1?Re+What+s+the+differences+among+Count+0+Count+1+and+Count+SomeField+Re: What's the differences among Count(0), Count(1) and Count(SomeField) <p></p> <blockquote><span class="icon-blockquote"></span> <h4>TimoYang</h4> <p></p> <p>I lookedhowever I think now that count(1) will be automatically converted to count(*)and this will take sometime</p> <p><a href="http://stackoverflow.com/questions/1221559/count-vs-count1">http://stackoverflow.com/questions/1221559/count-vs-count1</a></p> <p>NOT SURE WHO IS RIGHT</p> <p></p> </blockquote> <p></p> <p>True. If you are using the count function with select query with tablename.</p> <p>False. if you using with only select without specifying the tablename.</p> <pre class="prettyprint">--true if using with select with table(s) select COUNT(1),COUNT(*) from tableName --false if using without select with table(s) select COUNT(1),COUNT(*)</pre> <p><br> <br> </p> <p></p> 2012-05-15T05:53:55-04:004980930http://forums.asp.net/p/1803267/4980930.aspx/1?Re+What+s+the+differences+among+Count+0+Count+1+and+Count+SomeField+Re: What's the differences among Count(0), Count(1) and Count(SomeField) <p></p> <blockquote><span class="icon-blockquote"></span> <h4>yrb.yogi</h4> <p></p> <pre class="prettyprint">--true if using with select with table(s) select COUNT(1),COUNT(*) from tableName --false if using without select with table(s) select COUNT(1),COUNT(*)</pre> <p></blockquote></p> <p>First many thanks!</p> <p>Second, However&hellip;&hellip;I don't know what you are saying&mdash;&mdash;I want to know what the relationship between them and the MOST DIFFERENCE&hellip;&hellip;</p> <p>And&nbsp;</p> <pre class="prettyprint">select COUNT(1),COUNT(*)</pre> <pre class="prettyprint">still gives me the same answer&mdash;&mdash;1</pre></blockquote> 2012-05-15T06:04:24-04:004980949http://forums.asp.net/p/1803267/4980949.aspx/1?Re+What+s+the+differences+among+Count+0+Count+1+and+Count+SomeField+Re: What's the differences among Count(0), Count(1) and Count(SomeField) <p></p> <blockquote><span class="icon-blockquote"></span> <h4>TimoYang</h4> <p></p> <p>Second, HoweverI don't know what you are sayingI want to know what the relationship between them and the MOST DIFFERENCE</p> <p>And&nbsp;</p> <pre class="prettyprint">select COUNT(1),COUNT(*)</pre> <pre class="prettyprint">still gives me the same answer&mdash;&mdash;1</pre> <p></p> </blockquote> <p></p> <p>There is no difference at all.</p> <p>If you using Count function without specifying the tablename (properly say, witout using any select query). it will return value one, because</p> <ul> <li>COUNT Function expects some expression as value </li><li>Any expression specify, than count function return value 1 (distinct value) </li></ul> 2012-05-15T06:16:58-04:004980994http://forums.asp.net/p/1803267/4980994.aspx/1?Re+What+s+the+differences+among+Count+0+Count+1+and+Count+SomeField+Re: What's the differences among Count(0), Count(1) and Count(SomeField) <p>So do you mean count(*) and count(1) or count(0)No differences at all</p> 2012-05-15T06:29:44-04:00