Count(*)/Count(0)/Count(1) - All are same and it gives the number of rows in the result set. But if you specify the column name, it gives the number of rows in the result set where the specified column is not null.
Why count(0) and count(1)——What does 0 and 1 mean here？And what's the MOST DIFFERENCE BETWEEN the two statements？
There is no difference.
you must see this
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
Hi, I believe count(0) is counting the first column, Count(1) would be the second column in the table.
Since your specifying the column here, it will only count the non-null records in the specified column. Count(*) will give you a true count of the rows in the table.
Please remember to Mark As Answer if helpful
Regards, Christiandev (@chrisdev80), MCPD Web (2 & 4) & MCTS Windows (www.ScoreDonkey.com)
Hi, I believe count(0) is counting the first column, Count(1) would be the second column in the table.
No. COUNT(0) & COUNT(1) does not specify column name at all.
0,1 & any thing inside are only expression say value..
SELECT COUNT(0) --return 1
SELECT COUNT(1) --return 1
SELECT COUNT(100) --return 1
SELECT COUNT(*) --return 1
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?
Please remember to Mark As Answer if helpful
Regards, Christiandev (@chrisdev80), MCPD Web (2 & 4) & MCTS Windows (www.ScoreDonkey.com)
TimoYang
Contributor
3732 Points
1275 Posts
What's the differences among Count(0), Count(1) and Count(SomeField)
May 14, 2012 01:34 AM|LINK
Hi all——
Several questions want to know：
What's select count(0) in MSSQL？
What's select count(1) in MSSQL？
What's select count(FieldName) in MSSQL？
How about their speed？And if FieldName is a primary key，why the speed is the slowest？I don't know why……？？？
Reguards！
HarryBrock
Member
11 Points
21 Posts
Re: What's the differences among Count(0), Count(1) and Count(SomeField)
May 14, 2012 02:21 AM|LINK
Well,
Count(1) will run faster than Count(*).
Also Count(1) will return the number of rows and not the data.
count(FieldName) will only count table where fiedName is not null.
This might help you http://www.techonthenet.com/sql/count.php
senthilwaits
Contributor
3832 Points
651 Posts
Re: What's the differences among Count(0), Count(1) and Count(SomeField)
May 14, 2012 02:54 AM|LINK
Count(*)/Count(0)/Count(1) - All are same and it gives the number of rows in the result set. But if you specify the column name, it gives the number of rows in the result set where the specified column is not null.
Senthil Kumar Sundaram
TimoYang
Contributor
3732 Points
1275 Posts
Re: What's the differences among Count(0), Count(1) and Count(SomeField)
May 14, 2012 05:11 AM|LINK
Hey all again——
Why count(0) and count(1)——What does 0 and 1 mean here？And what's the MOST DIFFERENCE BETWEEN the two statements？
yrb.yogi
Star
14460 Points
2402 Posts
Re: What's the differences among Count(0), Count(1) and Count(SomeField)
May 14, 2012 07:19 AM|LINK
There is no difference.
you must see this
.Net All About
nilsan
All-Star
16940 Points
3719 Posts
Re: What's the differences among Count(0), Count(1) and Count(SomeField)
May 14, 2012 07:25 AM|LINK
I would suggest you to check out below links, which will clear you doubts :
http://blog.sqlauthority.com/2008/02/26/sql-server-select-1-vs-select-an-interesting-observation/
http://beyondrelational.com/modules/2/blogs/116/posts/12000/sql-server-count-or-count1-which-is-better.aspx
Blog | Get your forum question answered | Microsoft Community Contributor 2011
christiandev
Star
8607 Points
1841 Posts
Re: What's the differences among Count(0), Count(1) and Count(SomeField)
May 14, 2012 08:03 AM|LINK
Hi, I believe count(0) is counting the first column, Count(1) would be the second column in the table.
Since your specifying the column here, it will only count the non-null records in the specified column. Count(*) will give you a true count of the rows in the table.
Regards, Christiandev (@chrisdev80), MCPD Web (2 & 4) & MCTS Windows (www.ScoreDonkey.com)
yrb.yogi
Star
14460 Points
2402 Posts
Re: What's the differences among Count(0), Count(1) and Count(SomeField)
May 14, 2012 08:19 AM|LINK
No. COUNT(0) & COUNT(1) does not specify column name at all.
0,1 & any thing inside are only expression say value..
SELECT COUNT(0) --return 1
SELECT COUNT(1) --return 1
SELECT COUNT(100) --return 1
SELECT COUNT(*) --return 1
.Net All About
nilsan
All-Star
16940 Points
3719 Posts
Re: What's the differences among Count(0), Count(1) and Count(SomeField)
May 14, 2012 08:33 AM|LINK
Very True.
Blog | Get your forum question answered | Microsoft Community Contributor 2011
christiandev
Star
8607 Points
1841 Posts
Re: What's the differences among Count(0), Count(1) and Count(SomeField)
May 14, 2012 08:34 AM|LINK
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?
Regards, Christiandev (@chrisdev80), MCPD Web (2 & 4) & MCTS Windows (www.ScoreDonkey.com)