COUNT_BIG

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Prakriti Agrawal » COUNT_BIG

46 posts, Page 4 of 5

«««1 2 3 4 5 »»

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, April 11, 2013 12:58 AM
Points: 174, Visits: 555

Basically, page 3 blew me away BigGrin

Post #879856

sistemas 95572

Posted Wednesday, March 10, 2010 5:54 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 02, 2011 11:32 AM
Points: 29, Visits: 28

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

kumar ravindra

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 Cool

1,2,and 3 are correct

Post #880711

Oleg Netchaev

Posted Thursday, March 11, 2010 8:49 AM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:53 AM
Points: 1,662, Visits: 1,709

ravindraee24 (3/10/2010)

why SELECT COUNT_BIG(*) not return same result????????????

i hope Cool

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

tilew-948340

Posted Friday, March 12, 2010 6:39 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, April 02, 2013 8:28 PM
Points: 555, Visits: 2,137

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

Peter Trast

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 BigGrin

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 BigGrin

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

jim.tinney

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

Jamie Longstreet-481950

Posted Sunday, March 21, 2010 8:02 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:48 AM
Points: 2,440, Visits: 714

Interesting question... adds to the sql vocabulary...

Jamie

Post #887069

Paul White

Posted Tuesday, March 30, 2010 8:14 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:02 AM
Points: 10,989, Visits: 10,534

Good question, thank you.

Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #892806

colyan

Posted Thursday, April 15, 2010 12:13 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, April 20, 2013 2:12 PM
Points: 28, Visits: 6

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 »

46 posts, Page 4 of 5

«««1 2 3 4 5 »»

Permissions