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.
  2. More T-SQL Functions (Apprentice): The T-SQL functions a developer reaches for every week, grouped by job. Clean up and take apart text, round numbers, move and format dates, and keep NULL from surprising you.

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.