Summing

  • Comments posted to this topic are about the item Summing

  • 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.

  • 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(*)

  • 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

  • DECLARE @t TABLE (id INT)

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

    sum_=NULL

    cnt_=0

  • 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!
  • This is a good question, provided the caveats mentioned previously.

    Thanks,

    Matt

  • 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."

  • 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

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

  • Hmmm. Interesting. Thanks.

  • Nice first 15 seconds of my workday. 🙂

    Thanks!

  • tks for the question

  • Nice and easy question. Thanks!

  • 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