Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

COUNT_BIG Expand / Collapse
Author
Message
Posted Tuesday, March 9, 2010 6:16 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 6, 2014 12:28 AM
Points: 176, Visits: 565
Basically, page 3 blew me away
Post #879856
Posted Wednesday, March 10, 2010 5:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 1:16 PM
Points: 29, Visits: 32
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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 6, 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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 12:49 PM
Points: 1,676, Visits: 1,754
ravindraee24 (3/10/2010)
why SELECT COUNT_BIG(*) not return same result????????????

i hope 1,2,and 3 are correct

There 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

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: 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

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: 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 Trast
Microsoft 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

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: 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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, June 28, 2014 8:50 AM
Points: 2,649, Visits: 766
Interesting question... adds to the sql vocabulary...

Jamie
Post #887069
Posted Tuesday, March 30, 2010 8:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 11,192, Visits: 11,097
Good question, thank you.



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #892806
Posted Thursday, April 15, 2010 12:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 12:07 PM
Points: 28, Visits: 14
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, null

i) select count_big(*) from @temp
ii) select count_big(num) from @temp
iii) select count_big(numdesc) from @temp
iv) select count_big(all numdesc) from @temp
v) select count_big(distinct numdesc) from @temp

and the same result were from
a) i) and ii)
b) iii), iv) and v)

Post #903768
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse