Aggregating Data in T-SQL

Aggregates turn many rows into answers: totals, averages, counts. You will use T-SQL functions, aggregate with COUNT, SUM and AVG, group results with GROUP BY, and filter groups with HAVING.

Updated

Common T-SQL Functions

  1. Common T-SQL Functions (Apprentice): T-SQL's built-in functions work on one row at a time. Join and change text, put a value in place of NULL, and pull apart dates and round numbers.

Aggregate Functions in T-SQL

  1. Calculations vs Aggregations (Apprentice): A calculation works within one row and returns a value for every row; an aggregation works across many rows and returns one value for all of them.
  2. Common Aggregate Functions (Apprentice): COUNT counts rows or values, SUM adds them, AVG averages them, and MIN and MAX find the smallest and largest. All but COUNT(*) skip NULL.
  3. Grouping Aggregations (Journeyman): GROUP BY splits the rows into groups and runs the aggregates once per group. Every column in the SELECT that isn't aggregated must be in the GROUP BY.
  4. HAVING (Journeyman): WHERE filters rows before they're grouped; HAVING filters the groups afterward, by their aggregates. Know which to use, and the order a query's clauses run in.

Exercise: Aggregating Data

  1. Exercise: Aggregating Data (Journeyman): Twelve questions about Northwind, answered with COUNT, SUM, AVG, MIN, MAX, GROUP BY and HAVING, each with the result to expect.