SQL Server Aggregate Functions

Aggregates collapse many rows into one value. T-SQL has the standard set -- COUNT, SUM, AVG, MIN, MAX -- plus a few of its own (COUNT_BIG, APPROX_COUNT_DISTINCT), a couple of famous integer-arithmetic traps, and no FILTER clause, which changes how you write conditional aggregation. This guide covers the functions, the NULL rules, and the grouping extensions (ROLLUP, CUBE, GROUPING SETS).

Sample Schema

CREATE TABLE orders (
    order_id    int          PRIMARY KEY,
    customer_id int          NULL,        -- NULL = guest checkout
    region      varchar(10)  NOT NULL,
    amount      int          NOT NULL,
    order_date  date         NOT NULL
);
 
INSERT INTO orders VALUES
    (101, 1,    'north', 250, '2026-01-05'),
    (102, 1,    'north', 120, '2026-01-20'),
    (103, 2,    'south', 400, '2026-02-02'),
    (104, NULL, 'north',  80, '2026-02-10'),
    (105, 3,    'south', 250, '2026-02-11');

COUNT(*) vs COUNT(column) vs COUNT(DISTINCT column)

Three different questions:

SELECT COUNT(*)                    AS all_rows,        -- 5
       COUNT(customer_id)          AS non_null_ids,    -- 4 (NULL skipped)
       COUNT(DISTINCT customer_id) AS unique_customers -- 3
FROM orders;

COUNT(*) counts rows. COUNT(column) counts rows where the column is not NULL. That difference is the point, not a quirk -- COUNT(customer_id) on the sample data answers "how many orders have a known customer".

COUNT returns int. Past 2,147,483,647 rows it throws an arithmetic overflow error -- use COUNT_BIG(*), which returns bigint. Indexed views with an aggregate require COUNT_BIG, which is where most people first meet it.

Aggregates Ignore NULL (and Warn About It)

Every aggregate except COUNT(*) skips NULLs. AVG divides by the count of non-NULL values, not the row count. SQL Server also emits a warning many drivers surface as noise:

Warning: Null value is eliminated by an aggregate or other SET operation.

That warning on SUM(col) means the column contains NULLs and they were skipped -- it's informational, but it breaks some legacy providers mid-resultset. SET ANSI_WARNINGS OFF suppresses it; fixing the data model or wrapping with ISNULL(col, 0) is usually better.

One related trap: SUM over zero rows (or all-NULL input) returns NULL, not 0. Wrap the whole aggregate when you need a number: ISNULL(SUM(amount), 0).

The AVG Integer Division Trap

AVG (and SUM/COUNT arithmetic) follows the input type. On an int column the average is computed in integer arithmetic and the fraction is silently discarded:

SELECT AVG(amount) FROM orders;                    -- 220 (true mean is 220.0 here, but...)
SELECT AVG(amount) FROM orders WHERE region='north'; -- 150, true mean 150.0
-- with amounts 250, 120, 80: AVG = 150 exactly. Change 120 to 125:
-- true mean 151.67 → AVG(amount) returns 151
SELECT AVG(CAST(amount AS decimal(12,2))) FROM orders;  -- decimal result

Cast the input, not the output -- CAST(AVG(amount) AS decimal) casts after the precision is already lost.

The SUM Overflow Trap

SUM over int returns int. A few hundred million realistic amounts overflow it:

Arithmetic overflow error converting expression to data type int.

Same fix, same rule: cast the input -- SUM(CAST(amount AS bigint)). SUM over bigint returns bigint; over decimal(p,s) it returns decimal(38,s), which practically never overflows.

MIN and MAX

Work on numbers, strings (collation order decides), and dates. Two idioms worth knowing:

• Most recent activity per group: MAX(order_date).
• "Value from the row with the max" is not what MAX does -- getting the amount of the latest order needs a window function (ROW_NUMBER filtered to 1) or CROSS APPLY ... ORDER BY ... DESC with TOP (1). See our window functions guide.

HAVING vs WHERE

WHERE filters rows before aggregation; HAVING filters groups after. Predicates on raw columns belong in WHERE -- it shrinks the work early and can use indexes. HAVING is for predicates on aggregates:

SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE order_date >= '2026-01-01'      -- row filter: before grouping
GROUP BY customer_id
HAVING SUM(amount) > 300;             -- group filter: after grouping

Column aliases from the SELECT list are not visible in HAVING (or WHERE, or GROUP BY) -- repeat the expression or use a derived table/CTE.

Conditional Aggregation: No FILTER Clause

PostgreSQL has agg(...) FILTER (WHERE ...); T-SQL does not. The idiom is CASE inside the aggregate:

SELECT region,
       COUNT(*)                                              AS orders,
       SUM(CASE WHEN amount >= 200 THEN amount ELSE 0 END)   AS big_order_revenue,
       COUNT(CASE WHEN customer_id IS NULL THEN 1 END)       AS guest_orders
FROM orders
GROUP BY region;

Note the COUNT(CASE ...) trick: without an ELSE, non-matching rows produce NULL and COUNT skips them. SUM(CASE WHEN ... THEN 1 ELSE 0 END) is the equivalent spelled differently. This is also the building block for pivoting -- see pivot tables in SQL Server.

ROLLUP, CUBE, GROUPING SETS

GROUP BY extensions that produce subtotal rows in one pass:

SELECT region, customer_id, SUM(amount) AS total
FROM orders
GROUP BY ROLLUP (region, customer_id);

ROLLUP (a, b) returns groups for (a, b), (a), and the grand total (). CUBE adds every combination. GROUPING SETS lets you list exactly the groupings you want:

GROUP BY GROUPING SETS ((region), (customer_id), ());

In subtotal rows the rolled-up column shows NULL -- indistinguishable from a real NULL in the data (the guest orders here). Disambiguate with GROUPING(col), which returns 1 when the NULL is a subtotal marker:

SELECT CASE WHEN GROUPING(region) = 1 THEN 'ALL' ELSE region END AS region, ...

Prefer the standard GROUP BY ROLLUP (a, b) syntax over the legacy GROUP BY a, b WITH ROLLUP, which is deprecated.

APPROX_COUNT_DISTINCT (2019+)

COUNT(DISTINCT col) on very large tables is memory-hungry because it must de-duplicate exactly. APPROX_COUNT_DISTINCT(col) (SQL Server 2019+) uses HyperLogLog: guaranteed within 2% error at 97% probability, with a small fixed memory footprint. Use it for dashboards over billions of rows where "about 12.3M users" is as good as the exact number. SQL Server 2022 added APPROX_PERCENTILE_CONT/APPROX_PERCENTILE_DISC in the same spirit.

Aggregates as Window Functions

Every aggregate also works with an OVER clause -- per-row totals without collapsing the result:

SELECT order_id, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total
FROM orders;

No GROUP BY involved, and the rules change (frames, ordering). That's a different tool -- covered in the window functions guide.

Common Mistakes

For the same ground in other dialects, see our PostgreSQL, MySQL, and SQLite aggregate guides. String aggregation (STRING_AGG) and its 8000-byte trap are covered in SQL Server string functions.

Writing grouped queries is faster when autocomplete knows your schema -- Mako's AI autocomplete suggests grouping columns and dialect-correct aggregates when you're connected to SQL Server. Try it free at mako.ai.