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 3:22 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, November 7, 2014 8:25 AM
Points: 910, Visits: 179
i think you are incorrect as from this simple example script (im using sql2005)

declare @t table(i int, x int)


insert into @t
select 1, null
union
select 3, 1
union
select 2, 1


select *
from @t

select count_big(*)
from @t

select count_big(x)
from @t

it shows that count_big ignored the nulls in the second query
Post #879212
Posted Tuesday, March 9, 2010 4:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 6, 2012 1:19 AM
Points: 44, Visits: 11
I answered I and II and according to the explanation, it should be right. I need my point, I'm only on Grasshopper status! Need points...
Post #879247
Posted Tuesday, March 9, 2010 6:47 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
I too got it wrong because I needed a refresher regarding the evil side of NULL.

Good QotD - Thanks


Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #879316
Posted Tuesday, March 9, 2010 6:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 8:17 AM
Points: 2,917, Visits: 2,537
Good question - Thought that I knew the answer, and was wrong. Now, I have a question. I ran the following against an existing database to prove/disprove the answer, and found the following:

SELECT COUNT_BIG(*) FROM test_table
98481

SELECT COUNT_BIG(testfield2) FROM test_table
98481

SELECT COUNT_BIG(ALL testfield2) FROM test_table
98481

SELECT COUNT_BIG(DISTINCT testfield2) FROM test_table
28452

Based upon testing, though brief, and the answer, I would assume that the correct answer is 1, 2, and 3. The answer description states that ALL is the default, whether used or not thus selection 1 would use the default of ALL as shown in the above example, correct?


Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Post #879327
Posted Tuesday, March 9, 2010 6:57 AM
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: Today @ 6:21 PM
Points: 568, Visits: 76,507
Explaination is a little off.
COUNT_BIG(all column_2) will count all rows from the table (including duplicate & null rows).


I think you meant count_big(*). COUNT_BIG(all column_2) will not count the NULLs.
Post #879335
Posted Tuesday, March 9, 2010 7:10 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
count_big(*) will count every row in table, including rows with null values on every column, probably because it gets the amount of rows out of catalog?


Try this:

declare @t table(i int, x int)

insert into @t select 1, null union select 3, 1 union select 2, 1 union select null, null


select * from @t

select count_big(*) from @t

select count_big(x) from @t

count_big(*) returns 4, and count_big(x) returns 2.
Post #879346
Posted Tuesday, March 9, 2010 7:31 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 24, 2014 9:39 AM
Points: 1,328, Visits: 428
I was confused enough by the explanation that I had to try this myself. Two statements in the explanation are contradictory (emphasis mine):

... 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.
ALL is the default and hence COUNT_BIG(column 2) is same as COUNT_BIG(ALL column_2). This evaluates expression for each row in a group and returns the number of nonnull values. ...


II and III are correct, by the way.



Ed Leighton-Dick
MCITP, MCTS
Post #879365
Posted Tuesday, March 9, 2010 7:35 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:39 PM
Points: 1,382, Visits: 1,752
sjimmo (3/9/2010)
Good question - Thought that I knew the answer, and was wrong. Now, I have a question. I ran the following against an existing database to prove/disprove the answer, and found the following:

SELECT COUNT_BIG(*) FROM test_table
98481

SELECT COUNT_BIG(testfield2) FROM test_table
98481

SELECT COUNT_BIG(ALL testfield2) FROM test_table
98481

SELECT COUNT_BIG(DISTINCT testfield2) FROM test_table
28452

Based upon testing, though brief, and the answer, I would assume that the correct answer is 1, 2, and 3. The answer description states that ALL is the default, whether used or not thus selection 1 would use the default of ALL as shown in the above example, correct?


No. It appears that you have no NULL values in testfield2. Here's the breakdown:

(a) COUNT_BIG(*) is essentially a row count function -- it returns the number of rows returned, including those with NULL values in any column.
(b) COUNT_BIG(testfield2) will return the number of rows where testfield2 is NOT NULL.
(c) COUNT_BIG(ALL testfield2) is the same as the previous, as ALL is the default.
(d) COUNT_BIG(DISTINCT testfield2) will return the number of unique nonnull values of testfield2

So, (b) and (c) are always equal. If (a) is equal to (b) and (c), that means you have no NULLs in testfield2 in your result set. If (d) is equal to (b) and (c), that means you have no duplicate values in testfield2 in your result set. If they're all 4 equal, that means testfield2 is a column with all unique non-null values.
Post #879366
Posted Tuesday, March 9, 2010 8:30 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 6:38 AM
Points: 685, Visits: 277
r5d4 (3/9/2010)
the answer is I and II , not II and III.

does anyone audit these questions? i want my point (joke) :)

Me too, me too...
It seems that there are more than 2 with the right answer if II and III are correct, as I is then correct too??
Also, the question is a bit vague:From following data, which two queries will return identical results? (select all that apply)
Do I select only 2 answers or all that apply?

anyone, enlighten me as why the first answer is not the same as the 3rd answer? (just seen the answer in the reply above this, OK i don't get the points)
Post #879420
Posted Tuesday, March 9, 2010 10:11 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 17, 2014 1:28 PM
Points: 3,189, Visits: 1,271
I believe that this is what the explanation should have been.

The answers are: SELECT COUNT_BIG(ALL COlumn_2) and SELECT COUNT_BIG(column_2). COUNT_BIG(*) will count all rows from the table (including duplicate & null rows). COUNT_BIG(DISTINCT column_2) will evaluate distinct non-null values.

ALL is the default and hence COUNT_BIG(column 2) is same as COUNT_BIG(ALL column_2). This evaluates expression for each row in a group and returns the number of nonnull values.

COUNT_BIG is works like COUNT, only difference is that COUNT_BIG returns bigint data type.



Post #879524
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse