What's the count?

  • Slick84

    SSCertifiable

    Points: 5602

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

    --
    :hehe:

  • SQLRNNR

    SSC Guru

    Points: 281252

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

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

  • jshailendra

    SSCrazy

    Points: 2260

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

  • aamirjani4u

    SSC Rookie

    Points: 39

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

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

    NumberOfRows * 0 = 0

  • Diyas

    SSCarpal Tunnel

    Points: 4845

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

  • kaspencer

    SSCarpal Tunnel

    Points: 4453

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

    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

  • Daniel Bowlin

    SSC-Dedicated

    Points: 34566

    This one definitely made me think a minute or two.

  • cajun_sql

    SSCommitted

    Points: 1842

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

  • wall str33t

    SSC Veteran

    Points: 253

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

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

    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

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

    Good Question!

    Converting oxygen into carbon dioxide, since 1955.

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

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