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

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)

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 ` is specified, then let T be the result of the preceding ; otherwise, let T be the result of the preceding .


2) Case:


a) If there are no grouping columns, then the result of
the 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.

StackExchange Database Administrators Q#92126, answer score: 6