Aggregates and grouping, the T-SQL way

GROUP BY collapses rows that share the grouping columns into one row, and every column in the SELECT list must then be either a grouping column or inside an aggregate. SQL Server enforces that strictly (error 8120) and never picks an arbitrary value for you, as MySQL does when its ONLY_FULL_GROUP_BY mode is switched off.

AVG of an integer is an integer

In T-SQL the result type of AVG follows its input. AVG over an INT column returns an INT, with the fraction thrown away, and 7 / 2 is 3 for the same reason. Make one side decimal first: AVG(score * 1.0) or AVG(CAST(score AS DECIMAL(10,2))).

WHERE filters rows, HAVING filters groups

The logical order is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. A condition on a plain column belongs in WHERE, so the rows are removed before grouping. A condition on an aggregate — "teams with at least two members" — can only go in HAVING, because the count does not exist until the groups do. That order is also why a column alias defined in SELECT cannot be used in WHERE or HAVING, only in ORDER BY.

COUNT(*) counts rows. COUNT(col) counts non-NULL values of col. COUNT(DISTINCT col) counts distinct non-NULL values.

Subtotals: ROLLUP and GROUPING()

GROUP BY ROLLUP (region, product) produces the normal groups, a subtotal per region and a grand total, in one pass. In the extra rows the rolled-up columns are NULL, which is ambiguous if the data itself has NULLs; GROUPING(col) returns 1 exactly when the NULL was made by the rollup. GROUPING SETS lets you list the combinations you want explicitly — is shorthand for — and produces all of them. The sets need not nest. Below, one pass over a ticket table counts per team, per priority (across teams) and overall, and the data has tickets whose priority really is NULL:

STRING_AGG in a defined order

STRING_AGG(name, ', ') concatenates a group's values. Without WITHIN GROUP (ORDER BY ...) the order of the items is not defined. It skips NULLs, and it has no DISTINCT option: de-duplicate in a subquery first.

Common mistakes

• Trusting AVG on an INT column. The first example averaged 1, 2 and 2 and printed 1.
• Filtering an aggregate in WHERE. SQL Server rejects the query (error 147); the condition goes in HAVING.
• Filtering a plain column in HAVING. It works, but every row is grouped first and the intent is harder to read.
• COUNT(*) when you meant "how many have a value". COUNT(bonus) and COUNT(*) differ by the number of NULL bonuses.

What interviewers ask

• What is the difference between WHERE and HAVING? WHERE filters rows before grouping; HAVING filters groups after aggregates are computed.
• Why does AVG(score) return 2 for scores 2 and 3? The input is an integer, so the result is an integer; convert to decimal first.
• How do you tell a subtotal row from a group whose key is NULL? GROUPING(col) = 1 marks the rows the rollup added.