Why does SUM() return 0 when no rows in table?

Question

I've been saying for years that if you are assigning an aggregate value to a variable that you need to handle if there are no rows returned. ie:

DECLARE @MyVar INT
SELECT @MyVar = SUM(t.MyValue) FROM dbo.MyTable t WHERE /* clause */
IF @MyVar IS NULL
 SET @MyVar = 0

However a developer recently pointed out that they don't need to do the @@ROWCOUNT check as the SUM always returns a value (even if there are no rows). Upon doing some further digging I've found that there seems to be inconsistent behaviour from SQL Server:

So if I run:

DECLARE @MyTable TABLE(ID INT, MyValue INT)
/* you get a value of 0 back */
SELECT ISNULL(SUM(t.MyValue),0) FROM @MyTable t WHERE t.ID = 100

I get a single row with a value of 0 back.

However if I add on a GROUP BY clause:

DECLARE @MyTable TABLE(ID INT, MyValue INT)
/* when you add on a GROUP BY, you no longer get a record back */
SELECT ISNULL(SUM(t.MyValue),0) FROM @MyTable t WHERE t.ID = 100 GROUP BY t.ID

I get no rows back (which is what I expected), I've done some digging into the MS documentation but can find no reference to this difference in behaviour. Can anyone explain why this is occuring? Also is this a change in how SQL Server works?

Note: My tests have been in SQL Server 2008R2

** Edit: looked back on some emails I'd sent in the past and corrected my check, I've been saying If the value is still NULL handle it... (handles no rows as well as assigned NULL)

• ypercubeᵀᴹ: Related: What is the correct result for this query?
• Andrew Bickerton: Another related: dba.stackexchange.com/questions/25435/…

Answer 1

The result is correct, i.e. according to the standards, as far as I can understand it.

The related question: What is the correct result for this query? contains the paragraph that explains it:

The following is an excerpt from the General Rules of 7.9 (the definition of the GROUP BY clause)

1) If no <where clause> is specified, then let T be the result of the preceding <from clause>; otherwise, let T be the result of the preceding <where clause>.

2) Case:

a) If there are no grouping columns, then the result of the <group by clause> is the grouped table consisting of T as its only group.

So, when there is no GROUP BY - or when we have GROUP BY () - the result should be a single group. It doesn't matter if the table is empty or not. You'll get a single row^* in the result.
If the table is empty (and the SUM() function is used), you get a single row with NULL as value - which you convert to 0 using ISNULL().

When you have GROUP BY t.id, you'll have one row for every group. But there are 0 groups in your case, so 0 rows in the result.

*_{: SQL-Server will however return 0 rows for the query with GROUP BY (), which is contradicting the standard.}

• Andrew Bickerton: My memory is that SQL Server 2005 and earlier used to not return any row if no GROUP BY was specified (a google and stackoverflow search indicates that other people had the same experience), do you know when/why it changed?
• ypercubeᵀᴹ: @AndrewBickerton Sorry, no, I don't know. And I haven't got a 2005 instance to test.
• Aaron Bertrand: @Andrew Not true. 2000 RTM and 2005 RTM both return 0, not an empty set, given the sample in the question.
• ypercubeᵀᴹ: @AndrewBickerton They got NULL, the same as you. You just use ISNULL() and convert the null to 0.
• Andrew Bickerton: @ypercube You're right, my mistake (I'd just deleted my comment because I was wrong)