Skip to content
Tutorials Center
My learning

Topics

Lesson 9 of 39 ยท T-SQL Querying

Grouping and Aggregating Data in SQL Server

Summarize data with COUNT, SUM, AVG, MIN, MAX, GROUP BY and HAVING, then go further with GROUPING SETS, ROLLUP, CUBE, STRING_AGG and COUNT_BIG in T-SQL.

Reports rarely need raw rows; they need totals, averages and counts per customer, per month, per product. Aggregate functions collapse many rows into one value, and GROUP BY decides which rows are collapsed together. This lesson covers the standard aggregates, grouping rules, filtering groups with HAVING, subtotals with ROLLUP, CUBE and GROUPING SETS, and the T-SQL-specific STRING_AGG.

The aggregate functions

Function Returns
COUNT(*) number of rows
COUNT(col) number of rows where col is not NULL
COUNT(DISTINCT col) number of distinct non-null values
SUM(col), AVG(col) total and mean of non-null values
MIN(col), MAX(col) smallest and largest value (works on text and dates too)
STDEV, VAR statistical spread
COUNT_BIG(*) like COUNT, but returns BIGINT

Aggregates ignore NULL values except COUNT(*). COUNT returns an INT and overflows beyond 2.1 billion rows; use COUNT_BIG for huge tables. Also beware integer division: AVG of an INT column returns an INT, so AVG(quantity) of 1, 2 returns 1, not 1.5. Cast first: AVG(CAST(quantity AS DECIMAL(10,2))).

SQL Whole-table aggregates ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Without GROUP BY, an aggregate query returns exactly one row.

GROUP BY

GROUP BY splits the rows into groups, one per distinct combination of the listed columns, and each aggregate is computed per group:

SQL One row per country ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The key rule: every column in the SELECT list must be either in GROUP BY or inside an aggregate function. Otherwise SQL Server reports "column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause". That is not a nuisance but a logical requirement: if you group by country, which first_name would the group show?

You can group by several columns, and by expressions, as long as the select list uses the same expression:

SQL
SELECT YEAR(ordered_at) AS order_year,
       MONTH(ordered_at) AS order_month,
       COUNT(*)          AS orders
FROM dbo.orders
GROUP BY YEAR(ordered_at), MONTH(ordered_at)
ORDER BY order_year, order_month;

T-SQL does not allow a SELECT alias in GROUP BY (grouping happens before SELECT), so repeat the expression. The portable strftime below plays the role of YEAR and MONTH in the editor.

SQL Orders per month and status ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Filtering groups with HAVING

WHERE filters rows before grouping; HAVING filters groups after aggregation. Because it runs after, it can use aggregates:

SQL WHERE versus HAVING ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Rule of thumb: if a condition does not need an aggregate, put it in WHERE; it removes rows early and is cheaper.

STRING_AGG: values into one string

Since SQL Server 2017 you can concatenate the values of a group into one string (older versions needed the FOR XML PATH trick):

SQL
SELECT category_id,
       STRING_AGG(name, N', ') WITHIN GROUP (ORDER BY name) AS product_list
FROM dbo.products
GROUP BY category_id;

WITHIN GROUP (ORDER BY ...) controls the order inside the string. The editor's engine spells it group_concat, so it is shown as a labelled stand-in:

SQL Stand-in: STRING_AGG as group_concat ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Subtotals: ROLLUP, CUBE and GROUPING SETS

T-SQL can compute several levels of grouping in one pass:

SQL
SELECT category_id, status, COUNT(*) AS n
FROM dbo.sales_view
GROUP BY ROLLUP (category_id, status);
  • ROLLUP (a, b) returns groups for (a, b), (a), and the grand total ().
  • CUBE (a, b) returns every combination: (a, b), (a), (b), ().
  • GROUPING SETS ((a), (b)) returns exactly the sets you list.

In the result, the subtotal rows show NULL in the rolled-up columns. To tell a real NULL from a subtotal marker use GROUPING(col), which returns 1 for a subtotal row. You can also write a manual version with UNION ALL, as the editor does here (SQLite lacks ROLLUP):

SQL Stand-in: a ROLLUP with UNION ALL ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The order of clauses, one more time

FROM then WHERE then GROUP BY then HAVING then SELECT then ORDER BY. This explains why you cannot use SUM(...) in WHERE, and why ORDER BY SUM(price) DESC is valid. Another useful consequence: a query with aggregates and no GROUP BY that matches no rows still returns one row (COUNT is 0, others are NULL), but a grouped query with no matching rows returns none.

Common mistakes

  • Selecting an ungrouped column. Add it to GROUP BY or aggregate it.
  • Putting an aggregate in WHERE. Use HAVING.
  • Integer averages. Cast to DECIMAL before AVG.
  • COUNT(col) when you meant COUNT(*). The first skips NULLs.
  • Joining before aggregating and multiplying rows, which inflates sums. Aggregate in a subquery or CTE first.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the difference between COUNT(*) and COUNT(col)?

    COUNT(*) counts rows; COUNT(col) counts only the rows where col is not NULL.
  2. 2. Which clause can filter on SUM(total) > 1000?

    HAVING runs after grouping, so it can use aggregates. WHERE runs before and cannot.
  3. 3. What does AVG return for an INT column holding 1 and 2?

    AVG of an integer column uses integer arithmetic and returns 1. Cast to DECIMAL to get 1.5.
  4. 4. What does ROLLUP (a, b) add compared with GROUP BY a, b?

    ROLLUP adds hierarchical subtotal rows (for a, then for everything). CUBE would add every combination.
  5. 5. Which function concatenates the values of a group into one string in SQL Server 2017+?

    STRING_AGG is the T-SQL aggregate. GROUP_CONCAT is MySQL and LISTAGG is Oracle.

Summary

  • Aggregates (COUNT, SUM, AVG, MIN, MAX) collapse rows; most ignore NULL.
  • Every selected column must be grouped or aggregated; aliases are not allowed in GROUP BY.
  • WHERE filters rows, HAVING filters groups.
  • STRING_AGG builds lists; ROLLUP, CUBE and GROUPING SETS produce subtotals.
  • Cast integers before AVG, and use COUNT_BIG for huge tables.

Next: String Functions