Summing

  • BrewerAdam

    SSC Enthusiast

    Points: 175

    Comments posted to this topic are about the item Summing

  • Jostein Saethern

    SSCommitted

    Points: 1861

    It will return the same if the table has data.

    If the table is empty, sum(1) will return Null where count(*) would return 0, so not always exactly the same.

  • vk-kirov

    SSCertifiable

    Points: 7686

    Explanation


    The sum(1) returns exactly the same as count(*).

    This is incorrect as Jostein already explained.

    Explanation


    Both have the same execution plan as well.

    This is incorrect too. Execute the following script and you will see the difference.

    SET SHOWPLAN_TEXT ON;

    GO

    SELECT SUM(1) FROM TestDatabase.dbo.TestTable;

    SELECT COUNT(*) FROM TestDatabase.dbo.TestTable;

    GO

    SET SHOWPLAN_TEXT OFF;

    GO

    Here is the first execution plan.

    |--Compute Scalar(DEFINE: ([Expr1004]=CASE WHEN [Expr1005]=(0) THEN NULL ELSE [Expr1006] END))

    |--Stream Aggregate(DEFINE: ([Expr1005]=Count(*), [Expr1006]=SUM((1))))

    |--Table Scan(OBJECT: ([TestDatabase].[dbo].[TestTable]))

    Here is the second execution plan.

    |--Compute Scalar(DEFINE: ([Expr1004]=CONVERT_IMPLICIT(int,[Expr1005],0)))

    |--Stream Aggregate(DEFINE: ([Expr1005]=Count(*)))

    |--Table Scan(OBJECT: ([TestDatabase].[dbo].[TestTable]))

    So the first execution plan can be written using the following pseudocode:

    if count(*) == 0

    QueryResult = NULL

    else

    QueryResult = sum(1)

    While the second execution plan does not contain such condition:

    QueryResult = count(*)

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Aside from the small inconsistencies mentioned earlier, a fine question. A bit too easy though 😀

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • ako58

    Hall of Fame

    Points: 3262

    DECLARE @t TABLE (id INT)

    SELECT SUM(1) AS sum_, COUNT(*) AS cnt_ FROM @t

    sum_=NULL

    cnt_=0

  • rfr.ferrari

    SSCertifiable

    Points: 6879

    easy question! but you must have careful with the affirmations!!!!


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • Mattrick

    Ten Centuries

    Points: 1176

    This is a good question, provided the caveats mentioned previously.

    Thanks,

    Matt

  • Dave62

    SSCertifiable

    Points: 6462

    Thanks for the question.

    Another answer that may have fooled a few could have been: "1 row with the sum of all the values from the 1st column of every record in the table."

  • TomThomson

    SSC Guru

    Points: 104772

    Nice question, but only works for non-empty tables.

    The reason it doesn't work for empty tables is an excellent illustration of SQL's idiotic confustion between emptiness and nullity - only a committee of mathematical illiterates could have decided that teh sum of the empty set should not be zero.

    Tom

  • DBA_Dom

    SSCrazy

    Points: 2842

    I needed this simple question this morning, ignoring the basic issues mention already as can be seen in the answer selection.

  • Daniel Bowlin

    SSC-Dedicated

    Points: 34566

    Hmmm. Interesting. Thanks.

  • Revenant

    SSC-Forever

    Points: 42467

    Nice first 15 seconds of my workday. 🙂

    Thanks!

  • OzYbOi d(-_-)b

    Hall of Fame

    Points: 3944

    tks for the question

  • Ken Wymore

    SSCoach

    Points: 16415

    Nice and easy question. Thanks!

  • jlennartz

    SSCommitted

    Points: 1574

    Sorry, but I don't see where the reference given supports the explaination.

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

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