SQL Server Aggregate Functions: COUNT, SUM, AVG, STRING_AGG and the NULL Traps

Aggregate functions turn many rows into one value: a count, a total, an average, a minimum or maximum, a list. They’re the basis of every report and dashboard, and they look simple. Most of the bugs they cause come from a handful of details: how NULL is handled, integer arithmetic, overflow, and the difference between COUNT(*) and COUNT(column).

This guide covers the aggregate functions available in SQL Server 2025, how COUNT really works, the traps in SUM and AVG, GROUP BY and HAVING, conditional aggregation, grouping sets, STRING_AGG, approximate aggregates for large tables, and aggregates as window functions.

Aggregate Functions in SQL Server

Two rules apply to all of them. Aggregates ignore NULL values (except COUNT(*), which counts rows). And in a query with GROUP BY, every column in the SELECT list must be either in the GROUP BY or inside an aggregate.

COUNT(*), COUNT(1) and COUNT(column)

These three are often treated as the same thing. Two of them are; the third isn’t:

CREATE TABLE #Office (id int, name varchar(50));
INSERT INTO #Office VALUES (1, 'Istanbul'), (2, 'Ankara'), (3, NULL), (4, 'Ankara');

SELECT
    COUNT(*)             AS all_rows,          -- 4
    COUNT(1)             AS all_rows_too,      -- 4
    COUNT(name)          AS non_null_names,    -- 3
    COUNT(DISTINCT name) AS distinct_names     -- 2
FROM #Office;

• COUNT(*) counts rows, including rows where every column is NULL.
• COUNT(1) returns exactly the same result and gets exactly the same plan. The “1” isn’t a column number; it’s a constant that’s never NULL. There’s no performance difference.
• COUNT(column) counts only rows where that column isn’t NULL. Use it deliberately, not as a style choice.
• COUNT(DISTINCT column) counts distinct non-NULL values, which needs a sort or hash and is much more expensive on large tables.

COUNT returns int, so it fails with an arithmetic overflow above 2,147,483,647 rows. Use COUNT_BIG for tables that can get that large.

SUM and AVG: Integer and Overflow Traps

AVG of integers is an integer

The result type of AVG follows the input. The average of 1, 2 and 2 is 1.67, but on an int column SQL Server returns 1:

SELECT AVG(Quantity)                       AS int_avg,      -- 1
       AVG(CAST(Quantity AS decimal(10,2))) AS decimal_avg  -- 1.67
FROM (VALUES (1), (2), (2)) AS t(Quantity);

SUM can overflow

A SUM over an int column returns int. When the total passes about 2.1 billion, the query fails with “Arithmetic overflow error converting expression to data type int” (error 8115). Cast before summing, not after:

SELECT SUM(CAST(LineAmount AS bigint)) AS total
FROM dbo.SalesLines;

NULL results

SUM, AVG, MIN and MAX return NULL when there are no rows or all values are NULL, not 0. Wrap the aggregate, not the column, when the report needs a zero: ISNULL(SUM(Amount), 0). Wrapping the column inside (SUM(ISNULL(Amount, 0))) changes AVG results, because NULLs that were ignored now count as zeros.

GROUP BY, WHERE and HAVING

WHERE filters rows before grouping; HAVING filters groups after the aggregates are calculated. Filter in WHERE whenever you can: fewer rows go into the grouping, and indexes on the filter columns can be used.

SELECT CustomerID,
       COUNT(*)    AS order_count,
       SUM(Amount) AS total_amount
FROM dbo.Orders
WHERE OrderDate >= '20260101'          -- row filter
GROUP BY CustomerID
HAVING SUM(Amount) > 100000            -- group filter
ORDER BY total_amount DESC;

Conditional Aggregation

A CASE inside an aggregate counts or sums only the rows that match, which turns several queries into one pass over the table and is the simplest way to pivot:

SELECT
    CustomerID,
    COUNT(*)                                           AS all_orders,
    COUNT(CASE WHEN Status = 'Cancelled' THEN 1 END)   AS cancelled_orders,
    SUM(CASE WHEN YEAR(OrderDate) = 2025 THEN Amount END) AS amount_2025,
    SUM(CASE WHEN YEAR(OrderDate) = 2026 THEN Amount END) AS amount_2026
FROM dbo.Orders
GROUP BY CustomerID;

The CASE has no ELSE on purpose: non-matching rows become NULL, and COUNT and SUM ignore them.

Subtotals with ROLLUP, CUBE and GROUPING SETS

SELECT
    Region,
    Product,
    SUM(Amount)                       AS total,
    GROUPING(Region)                  AS is_grand_total,
    GROUPING(Product)                 AS is_region_total
FROM dbo.Sales
GROUP BY ROLLUP (Region, Product)
ORDER BY Region, Product;

ROLLUP adds a subtotal per region and a grand total; CUBE adds every combination; GROUPING SETS lets you list exactly the groupings you need. GROUPING tells subtotal rows apart from detail rows whose value happens to be NULL.

STRING_AGG: Lists in One Column

SELECT
    o.CustomerID,
    STRING_AGG(CONVERT(nvarchar(max), p.ProductName), N', ')
        WITHIN GROUP (ORDER BY p.ProductName) AS products
FROM dbo.Orders   AS o
JOIN dbo.Products AS p ON p.ProductID = o.ProductID
GROUP BY o.CustomerID;

Aggregates as Window Functions

Adding OVER() computes the aggregate without collapsing rows, so detail and totals appear side by side:

SELECT
    OrderID,
    CustomerID,
    Amount,
    SUM(Amount) OVER (PARTITION BY CustomerID)                AS customer_total,
    100.0 * Amount / SUM(Amount) OVER (PARTITION BY CustomerID) AS pct_of_customer,
    SUM(Amount) OVER (PARTITION BY CustomerID ORDER BY OrderDate, OrderID
                      ROWS UNBOUNDED PRECEDING)               AS running_total
FROM dbo.Orders;

Running totals like this replace one of the most common reasons for cursors; see our guide to cursors and set-based alternatives.

Aggregates on Large Tables

• Columnstore indexes are built for aggregation: compressed column segments, batch mode processing and aggregate pushdown in Enterprise edition. Reports over hundreds of millions of rows often go from minutes to seconds.
• Indexed views store pre-aggregated results and are kept up to date automatically. They need SCHEMABINDING and COUNT_BIG(*) when the view has GROUP BY, and they add cost to every write on the base tables.
• Approximate functions such as APPROX_COUNT_DISTINCT use far less memory than COUNT(DISTINCT) with a small, bounded error. Use them for dashboards where an exact value isn’t required.
• Index the grouping columns for rowstore tables: an index whose key matches GROUP BY lets SQL Server use a stream aggregate without sorting.

Common Aggregate Mistakes

• Using COUNT(column) when you meant to count rows, and silently missing rows with NULLs.
• AVG on integer columns, returning truncated averages.
• SUM on int columns in growing tables, which works until the total passes 2.1 billion.
• Filtering aggregates in WHERE (not allowed) or row conditions in HAVING (slower).
• ISNULL inside AVG, which turns unknown values into zeros and changes the average.
• COUNT(*) on a huge table just to show a row count, instead of reading partition stats.
• STRING_AGG on non-max strings that suddenly fail when a list gets long.