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

 COUNT_BIG Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, March 9, 2010 6:16 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, August 18, 2014 4:04 AM Points: 176, Visits: 567
 Basically, page 3 blew me away
Post #879856
 Posted Wednesday, March 10, 2010 5:54 AM
 SSC Rookie Group: General Forum Members Last Login: Wednesday, May 27, 2015 2:37 PM Points: 29, Visits: 33
 Oleg, thank you very much for the time and explanation, you couldn't have been clearer. i'm gonna play with your code at lunch time :p .I've learned a lot more than the count_big function out of this. TYVM again!Greetings!
Post #880104
 Posted Wednesday, March 10, 2010 11:08 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, October 29, 2014 10:25 PM Points: 123, Visits: 44
 why SELECT COUNT_BIG(*) not return same result????????????i hope 1,2,and 3 are correct
Post #880711
 Posted Thursday, March 11, 2010 8:49 AM
 SSCommitted Group: General Forum Members Last Login: Yesterday @ 5:16 AM Points: 1,683, Visits: 1,801
 ravindraee24 (3/10/2010)why SELECT COUNT_BIG(*) not return same result????????????i hope 1,2,and 3 are correctThere is an explanation by sknox on page 2 of this discussion, which clearly explains why 2 and 3 are the only correct options. Please read the post by sknox. There are more related posts after that, on page 3.Oleg
Post #881086
 Posted Friday, March 12, 2010 6:39 PM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Thursday, May 15, 2014 8:37 PM Points: 589, Visits: 2,437
 This was a very very good question. Event I read the remark section in SQL Server 2008 Books Online about Count_big(COUNT_BIG(*) returns the number of items in a group. This includes NULL values and duplicates.COUNT_BIG(ALL expression) evaluates expression for each row in a group and returns the number of nonnull values.COUNT_BIG(DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, nonnull values.), I got it wrong and did not understand why my answer was wrong until I had read all the 4 pages of reply...Thanks you all!
Post #882224
 Posted Monday, March 15, 2010 12:51 PM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Friday, June 13, 2014 3:03 PM Points: 594, Visits: 655
 I used deductive reasoning more than SQL skills to guess this right I assumed Number 1 would return both columns, the other 3 would return only column 2 and number 4 would include one NULL (doesn't DISTINCT include one NULL?) so I assumed that ALL was the default and would therefore make 2 and 3 have identical results. But I guessed Is my explanation close to being right? Peter TrastMicrosoft Certified ...(insert many literal strings here)Microsoft Design Architect with Alexander Open Systems
Post #883271
 Posted Thursday, March 18, 2010 5:50 PM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Thursday, July 17, 2014 4:25 PM Points: 511, Visits: 87
 r5d4 (3/9/2010)the answer is I and II , not II and III.does anyone audit these questions? i want my point (joke) :)That was my answer too but it's wrong. I forgot that ALL is the default value for first argument, not *. It can't be I and II, because (*) will count null values, none of the other options will.Cheers!
Post #886001
 Posted Sunday, March 21, 2010 8:02 AM
 SSCrazy Group: General Forum Members Last Login: Wednesday, May 4, 2016 6:32 AM Points: 2,938, Visits: 830
 Interesting question... adds to the sql vocabulary... Jamie
Post #887069
 Posted Tuesday, March 30, 2010 8:14 AM
 SSCrazy Eights Group: General Forum Members Last Login: Wednesday, December 7, 2016 6:42 AM Points: 9,932, Visits: 11,347
 Good question, thank you. Paul WhiteSQLPerformance.comSQLblog.com@SQL_Kiwi
Post #892806
 Posted Thursday, April 15, 2010 12:13 AM
 SSC Rookie Group: General Forum Members Last Login: Friday, July 10, 2015 12:42 AM Points: 30, Visits: 30
 The ans given here is wrong;i used here declare @temp table (num int, numdesc varchar(50))insert @temp select 1, 'one' union select 2, 'two' union select 3, null union select 4, 'four'union select 5, nulli) select count_big(*) from @tempii) select count_big(num) from @tempiii) select count_big(numdesc) from @tempiv) select count_big(all numdesc) from @tempv) select count_big(distinct numdesc) from @tempand the same result were froma) i) and ii)b) iii), iv) and v)
Post #903768

 Permissions