Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Prakriti Agrawal » COUNT_BIG
Add to briefcase
46 posts, Page 2 of 5
««12345»»»

COUNT_BIG Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
simon.whale
Posted Tuesday, March 09, 2010 3:22 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 7:01 AM
Points: 658, Visits: 149
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
Post #879212
antuan.steyn
Posted Tuesday, March 09, 2010 4:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 06, 2012 1:19 AM
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...
Post #879247
Tom Garth
Posted Tuesday, March 09, 2010 6:47 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 04, 2011 7:20 AM
Points: 977, Visits: 1,499
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
Post #879316
sjimmo
Posted Tuesday, March 09, 2010 6:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:15 AM
Points: 2,865, Visits: 2,467
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
Post #879327
DBA_Dom
Posted Tuesday, March 09, 2010 6:57 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 563, Visits: 59,086
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.
Post #879335
sistemas 95572
Posted Tuesday, March 09, 2010 7:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 02, 2011 11:32 AM
Points: 29, Visits: 28
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.
Post #879346
Ed Leighton-Dick
Posted Tuesday, March 09, 2010 7:31 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 8:23 AM
Points: 1,079, Visits: 386
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
Post #879365
sknox
Posted Tuesday, March 09, 2010 7:35 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:31 AM
Points: 1,041, Visits: 1,356
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.
Post #879366
erwin.oosterhoorn
Posted Tuesday, March 09, 2010 8:30 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 7:55 AM
Points: 524, Visits: 193
r5d4 (3/9/2010)
the answer is I and II , not II and III.

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

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)
Post #879420
kevin.l.williams
Posted Tuesday, March 09, 2010 10:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:52 AM
Points: 2,796, Visits: 1,125
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.


Post #879524
« Prev Topic | Next Topic »

Add to briefcase
46 posts, Page 2 of 5
««12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.