SQL Server / Basic / Lesson 5

Grouping and trustworthy aggregates

Build customer summaries whose totals, counts, and missing values have explicit meanings. Use GROUP BY, HAVING, conditional aggregation, and correct arithmetic.

About 5 min readContent reviewed 2026-10-23

On this page

Lesson goals

Distinguish row filters from group filters.
Explain COUNT and NULL behavior.
Validate summary grain and numeric precision.

Choose grouping keys from the question

GROUP BY changes the grain from individual input rows to one row per distinct grouping-key combination. A report “by customer and month” needs both attributes in its grouping definition. Grouping by customer name alone can merge separate accounts with identical names. Group by the stable identifier, then attach descriptive attributes carefully, or include them when their dependency is understood.

Every selected expression must either describe the group legally or be an aggregate. SQL Server does not silently choose an arbitrary ungrouped display name. This strictness protects a useful question: which name would a group containing several customers display? Start by listing expected group keys and calculate one group by hand before writing the complete query.

Distinguish rows from known values

COUNT(*) counts rows, while COUNT(expression) counts non-null values of that expression. SUM and AVG ignore NULL inputs; they do not interpret them as zero. The example has three orders but only two known amounts, totaling 60.00 and averaging 30.00. Counting known amounts answers a different question from counting orders.

For an empty input without GROUP BY, COUNT returns zero while SUM returns NULL. With GROUP BY there may be no group row at all. Decide whether the report should show missing groups and whether a zero display is appropriate. COALESCE can format a final empty sum, but replacing NULL with zero before AVG changes the denominator and therefore the metric.

sql Count rows and known monetary values

SELECT COUNT(*) AS orders,COUNT(Amount) AS known_amounts,
 SUM(Amount) AS total,AVG(Amount) AS average_known
FROM (VALUES (CAST(20 AS decimal(10,2))),
 (CAST(NULL AS decimal(10,2))),(40))v(Amount);

Expected output

orders known_amounts total average_known
3      2             60.00 30.000000

The average is 60 divided by two known values. Display scale may differ between tools without changing the numeric result.

Filter rows before groups and groups after

WHERE decides which rows participate in aggregation. HAVING decides which completed groups remain. A report of customers whose open-order total exceeds 50 should first restrict input to open orders, group by CustomerId, then apply HAVING SUM(Amount) > 50. Filtering individual amounts above 50 would discard small orders that together form a large customer balance.

The example retains customer 1 because two open orders total 70; the closed 200 order does not participate. Customer 2 totals 25 and fails HAVING. This distinction is semantic, even if an optimizer can rearrange safe predicates internally. Write the query so a reviewer can trace membership and group selection separately.

sql Filter input and completed totals separately

SELECT CustomerId,SUM(Amount) AS open_total
FROM (VALUES (1,N'Open',30),(1,N'Open',40),
 (1,N'Closed',200),(2,N'Open',25))o(CustomerId,Status,Amount)
WHERE Status=N'Open'
GROUP BY CustomerId
HAVING SUM(Amount)>50
ORDER BY CustomerId;

Expected output

CustomerId open_total
1          70

The 30 and 40 rows combine before HAVING is evaluated. The closed amount is excluded by WHERE.

Build several metrics from one input

Conditional aggregation places CASE inside an aggregate. SUM(CASE WHEN Status = N'Open' THEN Amount ELSE 0 END) totals qualifying amounts while retaining other rows for other metrics. For counting, SUM(CASE WHEN condition THEN 1 ELSE 0 END) counts qualifying input rows. Document whether unknown amounts should yield missing information or be treated as zero for that particular metric.

Numeric types matter: integer division truncates fractional results, and SUM(int) still returns int, so large totals may overflow. Cast deliberately to decimal for ratios and bigint for large integer counts or sums where appropriate. Guard a zero denominator with NULLIF and decide how the application displays the resulting NULL. A percentage with an undefined denominator should not accidentally look like a measured zero.

Reconcile totals at each boundary

Validate a summary by reconciling its total with the same filtered base rows. Add a case with no orders, a NULL amount, repeated amounts, and several lines per order. SUM(DISTINCT Amount) does not repair join multiplication: two legitimate orders of 50 would collapse to one value. Fix the grain before aggregation instead.

Report specifications should state currency, status scope, date convention, and treatment of cancelled or refunded orders. A mathematically correct SUM can still answer the wrong business question. Keep each metric definition close to its expression, and compare a few manually worked groups against the output before optimizing. Precision and membership are correctness requirements, not presentation details.

Common errors and fixes

Column is invalid in the select list.

Why it happens: An expression is neither aggregated nor included in GROUP BY.
How to fix it: Group at the intended key or aggregate the expression meaningfully.
Prevent it next time: Define the result grain before the projection.

Divide by zero error encountered.

Why it happens: A ratio denominator can be zero.
How to fix it: Use NULLIF(denominator,0) and define the missing-result display.
Prevent it next time: Test empty and all-zero groups.

Averages are unexpectedly low.

Why it happens: NULL was converted to zero before averaging.
How to fix it: Average known values or document a deliberate zero-imputation rule.
Prevent it next time: Specify the denominator explicitly.

Check your understanding

1. Three rows have amounts 0, 20, and NULL. Compute COUNT(*), COUNT(Amount), and AVG(Amount).

Show worked solution

They are 3, 2, and 10. Zero is a known value and participates in the average. NULL does not. Replacing NULL with zero first gives a different metric with denominator 3.

2. Two orders of 50 each have two lines apiece. Can SUM(DISTINCT header.Amount) fix a line join?