SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


COUNT_BIG


COUNT_BIG

Author
Message
simon.whale
simon.whale
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1172 Visits: 216
i think you are incorrect as from this simple example script (im using sql2005)

declare @t table(i int, x int)


insert into @t
select 1, null
union
select 3, 1
union
select 2, 1


select *
from @t

select count_big(*)
from @t

select count_big(x)
from @t

it shows that count_big ignored the nulls in the second query
antuan.steyn
antuan.steyn
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 11
I answered I and II and according to the explanation, it should be right. I need my point, I'm only on Grasshopper status! Need points... :-)
Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1253 Visits: 1499
I too got it wrong because I needed a refresher regarding the evil side of NULL.

Good QotD - Thanks

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

sjimmo
sjimmo
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3762 Visits: 2904
Good question - Thought that I knew the answer, and was wrong. Now, I have a question. I ran the following against an existing database to prove/disprove the answer, and found the following:

SELECT COUNT_BIG(*) FROM test_table
98481

SELECT COUNT_BIG(testfield2) FROM test_table
98481

SELECT COUNT_BIG(ALL testfield2) FROM test_table
98481

SELECT COUNT_BIG(DISTINCT testfield2) FROM test_table
28452

Based upon testing, though brief, and the answer, I would assume that the correct answer is 1, 2, and 3. The answer description states that ALL is the default, whether used or not thus selection 1 would use the default of ALL as shown in the above example, correct?

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
DBA_Dom
DBA_Dom
Say Hey Kid
Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)

Group: General Forum Members
Points: 678 Visits: 81110
Explaination is a little off.
COUNT_BIG(all column_2) will count all rows from the table (including duplicate & null rows).


I think you meant count_big(*). COUNT_BIG(all column_2) will not count the NULLs.
sistemas 95572
sistemas 95572
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 33
count_big(*) will count every row in table, including rows with null values on every column, probably because it gets the amount of rows out of catalog?


Try this:

declare @t table(i int, x int)

insert into @t select 1, null union select 3, 1 union select 2, 1 union select null, null


select * from @t

select count_big(*) from @t

select count_big(x) from @t

count_big(*) returns 4, and count_big(x) returns 2.
Ed Leighton-Dick
Ed Leighton-Dick
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1334 Visits: 506
I was confused enough by the explanation that I had to try this myself. Two statements in the explanation are contradictory (emphasis mine):

... COUNT_BIG(all column_2) will count all rows from the table (including duplicate & null rows). COUNT_BIG(DISTINCT column_2) will evaluate distinct non-null values.
ALL is the default and hence COUNT_BIG(column 2) is same as COUNT_BIG(ALL column_2). This evaluates expression for each row in a group and returns the number of nonnull values. ...


II and III are correct, by the way.


Ed Leighton-Dick
MCITP, MCTS

sknox
sknox
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2624 Visits: 2832
sjimmo (3/9/2010)
Good question - Thought that I knew the answer, and was wrong. Now, I have a question. I ran the following against an existing database to prove/disprove the answer, and found the following:

SELECT COUNT_BIG(*) FROM test_table
98481

SELECT COUNT_BIG(testfield2) FROM test_table
98481

SELECT COUNT_BIG(ALL testfield2) FROM test_table
98481

SELECT COUNT_BIG(DISTINCT testfield2) FROM test_table
28452

Based upon testing, though brief, and the answer, I would assume that the correct answer is 1, 2, and 3. The answer description states that ALL is the default, whether used or not thus selection 1 would use the default of ALL as shown in the above example, correct?


No. It appears that you have no NULL values in testfield2. Here's the breakdown:

(a) COUNT_BIG(*) is essentially a row count function -- it returns the number of rows returned, including those with NULL values in any column.
(b) COUNT_BIG(testfield2) will return the number of rows where testfield2 is NOT NULL.
(c) COUNT_BIG(ALL testfield2) is the same as the previous, as ALL is the default.
(d) COUNT_BIG(DISTINCT testfield2) will return the number of unique nonnull values of testfield2

So, (b) and (c) are always equal. If (a) is equal to (b) and (c), that means you have no NULLs in testfield2 in your result set. If (d) is equal to (b) and (c), that means you have no duplicate values in testfield2 in your result set. If they're all 4 equal, that means testfield2 is a column with all unique non-null values.
erwin oosterhoorn
erwin oosterhoorn
SSC Eights!
SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)

Group: General Forum Members
Points: 918 Visits: 387
r5d4 (3/9/2010)
the answer is I and II , not II and III.

does anyone audit these questions? i want my point (joke) Smile

Me too, me too...
It seems that there are more than 2 with the right answer if II and III are correct, as I is then correct too??
Also, the question is a bit vague:From following data, which two queries will return identical results? (select all that apply)
Do I select only 2 answers or all that apply?
:-)
anyone, enlighten me as why the first answer is not the same as the 3rd answer? (just seen the answer in the reply above this, OK i don't get the points)
kevin.l.williams
kevin.l.williams
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3419 Visits: 1323
I believe that this is what the explanation should have been.

The answers are: SELECT COUNT_BIG(ALL COlumn_2) and SELECT COUNT_BIG(column_2). COUNT_BIG(*) will count all rows from the table (including duplicate & null rows). COUNT_BIG(DISTINCT column_2) will evaluate distinct non-null values.

ALL is the default and hence COUNT_BIG(column 2) is same as COUNT_BIG(ALL column_2). This evaluates expression for each row in a group and returns the number of nonnull values.

COUNT_BIG is works like COUNT, only difference is that COUNT_BIG returns bigint data type.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search