Summing

Question

Post reply

Summing

BrewerAdam

SSC Enthusiast

Points: 175
More actions
November 21, 2011 at 8:20 pm

#260459

Comments posted to this topic are about the item Summing

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

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

Jostein Saethern SSCommitted Points: 1861 More actions · Answer 1

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 More actions · Answer 2

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: 259105 More actions · Answer 3

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 SSCarpal Tunnel Points: 4016 More actions · Answer 4

DECLARE @t TABLE (id INT)

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

sum_=NULL

cnt_=0

rfr.ferrari SSCertifiable Points: 6879 More actions · Answer 5

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 More actions · Answer 6

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

Thanks,

Matt

Dave62 SSCertifiable Points: 7459 More actions · Answer 7

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 More actions · Answer 8

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