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 09, 2010 6:16 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Thursday, October 10, 2013 9:40 PM Points: 174, Visits: 558
 Basically, page 3 blew me away
Post #879856
 Posted Wednesday, March 10, 2010 5:54 AM
 SSC Rookie Group: General Forum Members Last Login: Monday, October 28, 2013 4:57 AM Points: 29, Visits: 31
 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: Friday, April 06, 2012 2:38 AM Points: 116, Visits: 40
 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: 2 days ago @ 5:28 PM Points: 1,676, Visits: 1,728
 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: Wednesday, September 25, 2013 5:10 PM Points: 589, Visits: 2,436
 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, March 15, 2013 10:35 AM Points: 594, Visits: 654
 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: Wednesday, February 22, 2012 10:02 AM Points: 511, Visits: 83
 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: Monday, November 25, 2013 1:35 PM Points: 2,548, Visits: 745
 Interesting question... adds to the sql vocabulary... Jamie
Post #887069
 Posted Tuesday, March 30, 2010 8:14 AM
 SSChampion Group: General Forum Members Last Login: Today @ 6:15 AM Points: 11,052, Visits: 10,814
 Good question, thank you. Paul WhiteSQL Server MVPSQLblog.com@SQL_Kiwi
Post #892806
 Posted Thursday, April 15, 2010 12:13 AM
 SSC Rookie Group: General Forum Members Last Login: Tuesday, October 22, 2013 1:01 PM Points: 28, Visits: 8
 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