Why does SUM() return 0 when no rows in table?
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 &lt;where clause&gt; is specified, then let T be the result of the preceding &lt;from clause&gt;; otherwise, let T be the result of the preceding &lt;where clause&gt;.
    
    2) Case:
    
    a) If there are no grouping columns, then the result of
    the &lt;group by clause&gt; 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.