# Summing

SSC Enthusiast

Points: 175

• 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: 258965

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

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

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

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

tks for the question

• Ken Wymore

SSCoach

Points: 16588

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)