What's the count?

Question

What's the count?

Slick84

SSCertifiable

Points: 5602
More actions
March 10, 2010 at 9:18 pm

#217778

Comments posted to this topic are about the item What's the count?
--
:hehe:

Viewing 15 posts - 1 through 15 (of 20 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Thanks for the question. Nice job.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Arjun SreeVastsva SSCertifiable Points: 7141 More actions · Answer 2

Nice Question ....Today i learned this from Central...Good Question

jshailendra SSCrazy Points: 2260 More actions · Answer 3

Good question..never thought of using count(*) for 2 tables in one T-SQL...thanks for question

aamirjani4u SSC Rookie Points: 39 More actions · Answer 4

count is basically. Counting the each record of that table.

For Example. Table A has 3 rows then count = 3. if table B has 4 Rows then count=4.

If combine both tables then 3 * 4 = 12 rows and count = 12

Carlo Romagnano SSC-Insane Points: 22670 More actions · Answer 5

if one table has one o more rows and the other is void, the result is 0.

NumberOfRows * 0 = 0

Diyas SSCarpal Tunnel Points: 4851 More actions · Answer 6

In Point of Interview Very Intelligence Question ......Nice...hmmmmm My Answer is Correct............!

kaspencer SSCarpal Tunnel Points: 4880 More actions · Answer 7

That's is a good question, illustrating the need for careful joins!

For those that don't know, this kind of result is called a "cartesian product", and if you allow it to happen in a real-world application with even a few hundred rows in either table, you are in for something of an explosion of complaints from your users, to say the least.

Ken.

You never know: reading my book: "All about your computer" might just tell you something you never knew!
lulu.com/kaspencer

nigel. SSChampion Points: 11636 More actions · Answer 8

Very good question.

However I think that the explanation is a bit lacking, and could explain in more detail what is happening here and what a 'cross join'/'cartesian product' is.

Here are some links that may help:

Cross Join

Cartesian Product

Nigel
How to post questions

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 9

Daniel Bowlin

SSC-Dedicated

Points: 34566

March 11, 2010 at 8:08 am

#1132064

This one definitely made me think a minute or two.

cajun_sql SSCommitted Points: 1842 More actions · Answer 10

Nice one - thanks. I just "recovered" from a Cartesian Product, myself. 🙂

wall str33t SSC Veteran Points: 253 More actions · Answer 11

A better question might be is there a difference between count(*) and count(col).

Many people don't know the subtleties between them.

sknox SSChampion Points: 12388 More actions · Answer 12

wall str33t (3/11/2010)
A better question might be is there a difference between count(*) and count(col).
Many people don't know the subtleties between them.

Rather than re-answer that question, I'll provide a link to where you can find that information:

http://www.sqlservercentral.com/Forums/FindPost879366.aspx -- My post explaining how COUNT_BIG works

http://www.sqlservercentral.com/Forums/FindPost879538.aspx -- Oleg's excellent test setup illustrating my explanation

These are from the discussion of the March 9 QotD by agrawal.prakriti, about COUNT_BIG. The only difference between COUNT_BIG and COUNT is that COUNT_BIG returns a BIGINT rather than an INT. For official reference:

http://msdn.microsoft.com/en-us/library/ms190317.aspx -- Microsoft's documentation for COUNT_BIG

http://msdn.microsoft.com/en-us/library/ms175997.aspx -- Microsoft's documentation for COUNT

Feel free to write and submit a QotD based on this info.

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 13

Carlo Romagnano (3/11/2010)
if one table has one o more rows and the other is void, the result is 0.
NumberOfRows * 0 = 0

This is a very good feature of the count function, to still return 0 if there is nothing to count. This makes it different from other aggregates, which will return null if there is nothing to consider. For example,

use AdventureWorks;

go

declare @one_or_more table

(

DepartmentID smallint,

[Name] nvarchar(50)

);

declare @void table (DepartmentID smallint, [Name] nvarchar(50));

-- insert 10 records into @one_or_more and leave @void empty

insert into @one_or_more

select

top 10 DepartmentID, [Name]

from HumanResources.Department;

select count(*) RecordCount, min(A.DepartmentID) DepartmentID

from @one_or_more A, @void B;

go

resulting in

RecordCount DepartmentID

----------- ------------

0 NULL

illustrates the point. The cartesian product does not return anything because the @void has no records, the min(A.DepartmentID) is therefore null though the value of the first department in the @one_or_more is actually 1, but the count shines here as it still returns 0, not null. I am not saying that implementation of the count is better, but I find this feature extremely useful.

This is a very good question, really liked it.

Oleg

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 14

My definition of a good QOD: Anytime I get the correct answer 😀

Good Question!

Converting oxygen into carbon dioxide, since 1955.