COUNT_BIG

Question

Post reply

COUNT_BIG

Viewing 15 posts - 31 through 45 (of 45 total)

You must be logged in to reply to this topic. Login to reply

Open Minded SSCommitted Points: 1852 More actions · Answer 1

Open Minded

SSCommitted

Points: 1852

March 9, 2010 at 6:16 pm

#1131076

Basically, page 3 blew me away 😀

sistemas 95572 SSC Enthusiast Points: 135 More actions · Answer 2

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!

kumar ravindra Old Hand Points: 369 More actions · Answer 3

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

i hope:cool: 1,2,and 3 are correct

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 4

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

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 5

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!

Peter Trast SSCarpal Tunnel Points: 4392 More actions · Answer 6

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

jim.tinney Mr or Mrs. 500 Points: 559 More actions · Answer 7

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!

Jamie-2229 SSCrazy Eights Points: 8156 More actions · Answer 8

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

Jamie

Paul White SSC Guru Points: 150467 More actions · Answer 9

Good question, thank you.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

colyan SSC Journeyman Points: 75 More actions · Answer 10

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)

Avaneesh -388582 SSChasing Mays Points: 657 More actions · Answer 11

Hi,

I. SELECT COUNT_BIG(*)

II. SELECT COUNT_BIG(column_2)

III. SELECT COUNT_BIG(ALL column_2)

IV. SELECT COUNT_BIG(DISTINCT column_2)

I Answered this question selecting II,III and IV (which actually is a correct answer) guess what i was indicated as INCORRECT! Pls let me know if I am so.

coz all the three (2,3,4) have same output.

Paul White SSC Guru Points: 150467 More actions · Answer 12

Avaneesh -388582 (5/20/2010)
Hi,
I. SELECT COUNT_BIG(*)
II. SELECT COUNT_BIG(column_2)
III. SELECT COUNT_BIG(ALL column_2)
IV. SELECT COUNT_BIG(DISTINCT column_2)
I Answered this question selecting II,III and IV (which actually is a correct answer) guess what i was indicated as INCORRECT! Pls let me know if I am so.
coz all the three (2,3,4) have same output.

Wow! That's amazing! What code did you use to come to that conclusion? I used this:

DECLARE @QotD

TABLE (

Column_1 TINYINT NOT NULL,

Column_2 TINYINT NULL

);

INSERT @QotD VALUES (1, 1);

INSERT @QotD VALUES (2, NULL);

INSERT @QotD VALUES (3, 2);

INSERT @QotD VALUES (4, 2);

INSERT @QotD VALUES (5, NULL);

INSERT @QotD VALUES (6, 3);

INSERT @QotD VALUES (7, 4);

INSERT @QotD VALUES (8, 5);

INSERT @QotD VALUES (9, 6);

INSERT @QotD VALUES (10, 7);

--I.

SELECT COUNT_BIG(*) FROM @QotD;

--II.

SELECT COUNT_BIG(Column_2) FROM @QotD;

--III.

SELECT COUNT_BIG(ALL Column_2) FROM @QotD;

--IV.

SELECT COUNT_BIG(DISTINCT Column_2) FROM @QotD;

I Answered this question selecting II,III (which actually is a correct answer) guess what i was indicated as CORRECT! Pls let me know if I am so. coz all the two (2,3) have same output. :laugh:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Dhruvesh Shah SSCrazy Points: 2303 More actions · Answer 13

Dhruvesh Shah

SSCrazy

Points: 2303

September 27, 2010 at 5:22 pm

#1228094

All is by default so II & III are same expressions

terrykzncs Ten Centuries Points: 1324 More actions · Answer 14

I think this question could have been worded better and is incomplete.

shemu1990 SSC Rookie Points: 25 More actions · Answer 15

explination given here is not fully correct-->"SELECT COUNT_BIG(ALL COlumn_2) and SELECT COUNT_BIG(column_2). 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.

:discuss:

because --->

"COUNT_BIG(all column_2) will count all rows from the table including duplicate & Excluding null rows).

and

COUNT_BIG(*) will count all rows from the table (including duplicate & null rows).