Applies to SQL Server 2019, 2022 and 2025.

Aggregates on empty sets behave in a way that surprises people the first time they hit it in a report. Ask SQL Server to total an empty table and you don’t get zero rows back, and you don’t get zero either: you get one row containing NULL. Add a GROUP BY and the same query returns nothing at all. Both results are correct, and once you know the rule they are easy to predict.

How aggregates on empty sets work

There are two kinds of aggregate query, and they treat an empty input differently.

• Scalar aggregate: no GROUP BY (or GROUP BY ()). It always returns exactly one row, even when no rows go in.
• Grouped aggregate: GROUP BY one or more columns. It returns one row per group, and an empty input has no groups, so it returns no rows.

Within that single row from a scalar aggregate, the functions split into two camps:

• COUNT(*), COUNT(column) and COUNT_BIG return 0. There were no rows to count, and zero is a real answer.
• SUM, AVG, MIN, MAX, STDEV, VAR and STRING_AGG return NULL. There were no values, so there is no total, average or maximum to report.

The WHERE clause runs before the aggregate, so a filter that matches nothing counts as an empty set too, even when the table is full.

A worked example

This builds an empty table and runs the same aggregates three ways. It’s safe to run anywhere; it only uses a temporary table.

CREATE TABLE #Sales
(
    SaleID   int           NOT NULL,
    Region   varchar(10)   NOT NULL,
    Amount   decimal(10,2) NOT NULL
);

-- 1. Scalar aggregate: one row back
SELECT COUNT(*)    AS SaleCount,
       SUM(Amount) AS TotalAmount,
       MAX(Amount) AS BiggestSale
FROM #Sales;
-- SaleCount = 0, TotalAmount = NULL, BiggestSale = NULL

-- 2. Grouped aggregate: no rows back
SELECT Region,
       COUNT(*)    AS SaleCount,
       SUM(Amount) AS TotalAmount
FROM #Sales
GROUP BY Region;
-- (0 rows affected)

-- 3. Scalar aggregate with HAVING: the one row is filtered out
SELECT COUNT(*) AS SaleCount
FROM #Sales
HAVING COUNT(*) > 0;
-- (0 rows affected)

DROP TABLE #Sales;

The third query is worth a second look. HAVING without GROUP BY treats the whole input as one group, so the single row is produced and then removed by the HAVING test.

The common mistake

The mistake I see most is assuming a total of “nothing” is zero. Someone writes a report that adds up a day’s sales, it runs fine for months, and then a quiet bank holiday comes along. The total comes back as NULL, and anything downstream that does arithmetic on it also turns into NULL: a running balance, a percentage, a “target minus actual” column. Nothing errors, the numbers just go blank.

The fix for a scalar aggregate is to say what you want when there is nothing to add up:

SELECT ISNULL(SUM(Amount), 0) AS TotalAmount
FROM dbo.Sales
WHERE SaleDate = '20261225';

The second half of the mistake is reaching for ISNULL on a grouped query and expecting the missing groups to appear. ISNULL can only change a value in a row that exists, and a grouped aggregate over no data has no rows to change. If you need every region listed with a zero, start from the list of regions and join the sales to it:

SELECT r.Region,
       COUNT(s.SaleID)          AS SaleCount,
       ISNULL(SUM(s.Amount), 0) AS TotalAmount
FROM dbo.Regions AS r
LEFT JOIN dbo.Sales AS s
       ON s.Region = r.Region
      AND s.SaleDate = '20261225'
GROUP BY r.Region;

Note COUNT(s.SaleID) rather than COUNT(*) here. With a LEFT JOIN, a region with no sales still produces one joined row full of NULLs, so COUNT(*) would report 1 for it. Counting a column from the sales side gives the correct 0. I also keep the date test in the ON clause; putting it in the WHERE clause would throw away those unmatched rows and turn the LEFT JOIN back into an inner join.

Variables and empty aggregates

The one-row rule also matters when you assign to a variable. Compare these two:

DECLARE @Total decimal(10,2) = -1,
        @Last  decimal(10,2) = -1;

SELECT @Total = SUM(Amount) FROM dbo.Sales WHERE 1 = 0;   -- @Total becomes NULL
SELECT @Last  = Amount      FROM dbo.Sales WHERE 1 = 0;   -- @Last stays -1

SELECT @Total AS TotalAmount, @Last AS LastAmount;

The aggregate returns a row, so the variable is overwritten with NULL. The plain SELECT returns no rows, so the assignment never happens and the variable keeps whatever it held before. Inside a loop, that old value can look perfectly believable. If you need to know whether anything was found, check @@ROWCOUNT straight after the assignment or test with EXISTS first.

Watch out for

• SUM, AVG, MIN and MAX return NULL over an empty set, not 0. Wrap them in ISNULL or COALESCE when zero is the answer you want.
• Adding GROUP BY changes “one row with NULL” into “no rows”. ISNULL cannot bring missing groups back; drive the query from a table of the groups you want to see.
• Use COUNT(column) from the outer side of a LEFT JOIN, not COUNT(*), or empty groups count as 1.
• A WHERE clause that matches nothing makes the set empty even on a busy table.
• Assigning an aggregate to a variable always overwrites it, possibly with NULL; assigning a plain column from no rows leaves it untouched.
• Use EXISTS, not COUNT(*) > 0, when you only need to know whether a row is there. It can stop at the first match.

For the full list of aggregate functions and how each treats NULLs, see Microsoft Learn’s Aggregate functions (Transact-SQL). More of my T-SQL and DBA notes are collected in the SQL Server DBA Handbook, and if you’re tidying up report queries, T-SQL Performance Anti-Patterns is a good next read.

This topic came up in the SQLServerCentral Question of the Day: The Empty Aggregate.

Clive Roberts DBA