Exercise: Aggregating Data

Twelve questions about Northwind, answered with COUNT, SUM, AVG, MIN, MAX, GROUP BY and HAVING, each with the result to expect.

Journeyman · T-SQL · SQL · Updated

Northwind's managers have questions: how much stock is there, who are the best customers, which orders were the biggest? Each one is a single query, and each needs an aggregate.

Get the exercise code

Open this lesson's GitHub repository, github.com/ask-codey/exercise-aggregating-data, also linked from the Exercise box beside the lesson. Click Code, then Download ZIP, and unzip it into your bootcamp folder. Or clone it with Git.

There's one .sql file per question, numbered in order. Each holds the question as a comment, and an Expect line that says what the right answer looks like. Nothing here changes any data.

What to do

  1. Open SQL Server Management Studio, and connect to localhost.
  2. Choose File > Open > File... and open 01_Count_Products.sql.
  3. Choose Northwind in the database drop-down.
  4. Write your query under the question, select it, and press F5.
  5. Compare your result with the Expect line, then go on to the next file.
Questions Practice
1–6 One aggregate for the whole table: COUNT, SUM, AVG, MIN, MAX, and COUNT(DISTINCT ...)
7–8 One row per group with GROUP BY
9–11 Filtering groups with HAVING, and rows with WHERE, in the same query
12 Bonus: an aggregate of a calculation

Hints

  • One row, or one per something? One row for the whole table is an aggregate on its own; one row per year, per category or per customer is GROUP BY.
  • Which filter? Rows before counting: WHERE. Groups by their count or total: HAVING.
  • NULL counts differently. COUNT(*) counts rows; COUNT(column) skips NULL.
  • Money can show extra zeros. Some tools, such as sqlcmd, show money with four decimal places: 17250.0000 is the same answer as the 17250.00 in an Expect line.

When you're done

Every result matches its Expect line. Look back at questions 9 and 11: they're the queries that turn a table of 830 orders into a short list worth acting on.

Submit your exercise

The exercise is how you finish this lesson. When your work is done, put it on GitHub in a repo of your own, then submit that repo's address here.

  1. On GitHub, click +, then New repository. Name it (for example exercise-aggregating-data), choose Public, and click Create repository. Leave out the README: your folder already has one.

  2. In a terminal in your exercise folder (Git Bash on Windows), send your work to your new repo. If you downloaded the ZIP:

    git init -b main
git add .
git commit -m "My solution"
git remote add origin https://github.com/your-name/exercise-aggregating-data.git
git push -u origin main

    If you cloned the starter instead, point your copy at your own repo, then commit and push:

    git remote set-url origin https://github.com/your-name/exercise-aggregating-data.git
git add .
git commit -m "My solution"
git push -u origin main

  3. Paste your repo's address, such as https://github.com/your-name/exercise-aggregating-data, into Submit your exercise on this page. The site checks that the repo is public and holds your commits, and then the lesson is complete.

Changed something after you submitted? Push it, and submit again: your latest submission is the one that counts.

So what

Counting, totaling and ranking groups is most of what reports are made of. You can now answer a manager's "how many, how much, and which ones" in a single query.

Key terms

Related