Aggregate correctly

Lesson 5
45 min
Module 2: SQL essentials

Lesson 5 of 18 · SQL and Data Preparation for AIAggregate correctly

Course overview

1. Data sources and structure

2. SQL essentials

3. Useful analytical queries

4. Clean and transform data

5. Prepare for modelling safely

6. Data contracts and handoff

7. Case studies

What you will be able to do

Turn many rows into a few numbers that answer a question, such as tickets per team or the mean order value. Know what each number really counts. A count of rows is not always a count of tickets, and an average can include values that are wrong.

NotebookDo this lesson in a notebookColab · Kaggle · GitHub

The notebook has every query, task and check of this lesson, with the instructions. Its first cell downloads the practice data and builds the database, so it works without the local project from Module 1. Read the explanations here, and run the code there.

Open in Colab Open in Kaggle View on GitHub

Colab and Kaggle open your own copy. They need a free Google or Kaggle account. Stuck? See the solution notebook.

Before you startWhat to know and to have readyAbout 45 minutes

What you need to know

SELECT, WHERE, ORDER BY and IS NULL, from Select and filter.
That a comparison with NULL is unknown, so WHERE drops the row, from Select and filter: What WHERE keeps.

What you need on your computer (only for the local route; the notebook and the boxes on this page need none of it)

The folder ~/projects/ticket-data with larkfield.db and q.py. You made it in Set up a practice database: Build the database.
If the folder or the database is missing or changed, follow that section again, or reset the database with python3 get_data.py --reset.
A terminal open in that folder. Type python3 on macOS and Linux and python on Windows, as explained in Which Python command to type.
The saved queries of this module go in queries/, numbered from 21-, as in Select and filter: Guided practice. This lesson adds 23-. If you skipped that lesson, you do not need its files here.

Tested on macOS with Python 3.14.6 (SQLite 3.50.4). The browser boxes use SQLite 3.49.1. The notebook was tested in Jupyter.

Six orders, one average

An aggregation turns many rows into one value: a count, a total, a mean, a smallest or a largest value. SQL has a function for each: COUNT, SUM, AVG, MIN and MAX.

Here are six tickets with the value of the order that each one is about, in dollars:

ticket_id	team	order_value	What the value really is
T-1	delivery	40	an order of $40
T-2	delivery	60	an order of $60
T-3	returns	50	an order of $50
T-4	account	NULL	no order: an account question
T-5	returns	−1	“unknown” in the order system
T-6	delivery	5000	an order of $50, sent in cents

The aggregations of order_value over the six rows:

Expression	Result	Why
`COUNT(*)`	6	counts rows
`COUNT(order_value)`	5	counts rows where the column is not NULL
`SUM(order_value)`	5149	40 + 60 + 50 − 1 + 5000; NULL is left out
`AVG(order_value)`	1029.8	5149 ÷ 5: the sum divided by the non-NULL count
`MIN(order_value)`	−1	the smallest non-NULL value
`MAX(order_value)`	5000	the largest value

The real mean of the four known orders is (40 + 60 + 50 + 50) ÷ 4 = $50. SQL says 1029.8. Nothing failed: SQL did correct arithmetic on wrong values.

Each row teaches one rule:

NULL is left out of COUNT(column), SUM, AVG, MIN and MAX. Here this is right: T-4 has no order, so it must not count as an order of $0.
−1 is a sentinel value: a normal-looking value that stands for something else, here “unknown”. SQL cannot know this, so it adds −1 like any number.
5000 is in the wrong unit. One old form sent cents, not dollars. One value 100 times too large moves the mean much more than all the others.

MIN and MAX show both problems at once. That is why you print them next to every mean.

Run SQL in your browser

Try it: six orders

This box has only the six tickets above. Run the query and compare each value with the table. You should see 6, 5, 5149, 1029.8, −1 and 5000. Then add WHERE order_value <> -1 after FROM tickets. Predict how many rows COUNT(*) gives now, and why it is not 5.

SQL query (you can change it)

SELECT
    COUNT(*)           AS all_rows,
    COUNT(order_value) AS with_value,
    SUM(order_value)   AS total,
    AVG(order_value)   AS mean_value,
    MIN(order_value)   AS smallest,
    MAX(order_value)   AS largest
FROM tickets;

With WHERE order_value <> -1, COUNT(*) gives 4, not 5. The filter drops T-5, and it also drops T-4, because NULL <> -1 is unknown. This is the rule from the last lesson, inside an aggregation.

One value per group: GROUP BY

GROUP BY team puts the rows with the same team into one group. The aggregations then give one value per group, so the result has one row per team:

SELECT team, COUNT(*) AS ticket_rows, AVG(order_value) AS mean_value
FROM tickets
GROUP BY team;

Group	Rows in the group	`ticket_rows`	`mean_value`
account	T-4	1	NULL (no values to average)
delivery	T-1, T-2, T-6	3	(40 + 60 + 5000) ÷ 3 = 1700
returns	T-3, T-5	2	(50 − 1) ÷ 2 = 24.5

The result has 3 rows, and 1 + 3 + 2 = 6 rows went in. That is the check for any GROUP BY: the counts of the groups add up to the rows that went in. If a grouping column has NULL values, all of them form one group, with NULL as its name.

Every column in SELECT must be either in GROUP BY or inside an aggregation. SELECT team, ticket_id … GROUP BY team asks for one ticket_id for a group of three tickets. PostgreSQL stops with an error. SQLite answers and picks one of the three without a warning, so do not write it.

Three ways to count

COUNT has three forms, and on the real tickets table they give three different answers:

Expression	Counts	Result
`COUNT(*)`	rows	1,417
`COUNT(order_value)`	rows where `order_value` is not NULL	1,229
`COUNT(DISTINCT ticket_id)`	different values of `ticket_id`, NULL left out	1,403

COUNT(*) counts the 14 duplicate rows from the export twice; COUNT(DISTINCT ticket_id) counts each ticket once. When someone asks “how many tickets?”, the answer is 1,403. COUNT(order_value) is 188 less than COUNT(*): the 188 account tickets have no order.

COUNT(DISTINCT customer_id) gives 198. The 12 guest tickets have NULL there, and COUNT leaves NULL out, so guests are not “a customer”.

Run SQL in your browser

Try it: three ways to count

Predict with_channel before you run it: 38 rows have no channel. You should see 1417, 1229, 1403, 1379 and 198.

SQL query (you can change it)

SELECT
    COUNT(*)                    AS all_rows,
    COUNT(order_value)          AS with_order_value,
    COUNT(DISTINCT ticket_id)   AS tickets,
    COUNT(channel)              AS with_channel,
    COUNT(DISTINCT customer_id) AS customers
FROM tickets;

Worked example: the mean order value per team

Priya, the data scientist, plans an order_value feature for the model. She asks Mei: “What is a typical order value for each team?” Mei writes one row per team, with the counts next to the mean:

-- One row per team: rows, distinct tickets and order values.
SELECT
    team,
    COUNT(*)                  AS ticket_rows,
    COUNT(DISTINCT ticket_id) AS tickets,
    COUNT(order_value)        AS with_value,
    ROUND(AVG(order_value), 2) AS avg_value,
    MIN(order_value)          AS min_value,
    MAX(order_value)          AS max_value
FROM tickets
GROUP BY team
ORDER BY team;

ROUND(x, 2) rounds to two decimal places. It changes only how the result looks.

team      ticket_rows  tickets  with_value  avg_value  min_value  max_value
--------  -----------  -------  ----------  ---------  ---------  ---------
account   188          188      0           NULL       NULL       NULL
delivery  452          449      452         264.45     -1.0       17319.0
payment   241          240      241         213.79     -1.0       8554.0
returns   327          321      327         262.24     -1.0       12276.0
warranty  209          205      209         535.11     -1.0       34518.0

5 rows

Mei reads it from left to right, and checks each column:

Rows: 188 + 452 + 241 + 327 + 209 = 1,417. Every row is in exactly one group.
Tickets: 188 + 449 + 240 + 321 + 205 = 1,403. The duplicate rows are in four teams.
With value: the account team has 0. Grace confirms: “Account questions have no order.” Here a missing value has a meaning. It is not “unknown”.
Min: −1 in four teams. In the order system, −1 means “unknown”. It is a sentinel, not a refund. The table has 11 such rows.
Max: up to 34,518 dollars, for a garden shop. Mei looks at the largest rows with ORDER BY order_value DESC. All 25 rows above 2,000 came from the old web form (web_form or webform), which sent cents until February 2026.

So the warranty mean of 535.11 is not a typical order. Across the whole table, AVG(order_value) is 299.95. As a quick check, Mei leaves out the 11 rows of −1 and the 25 rows above 2,000. The mean of the other 1,193 values is 123.93.

That check is not the fix. It drops 25 real orders only because their unit is wrong. And the limit of 2,000 is a guess that works on this export only. Module 4 converts the cents rows and records the sentinel as unknown, with a flag on each changed row. For now, Mei writes to Priya: “Do not use order_value before cleaning. The mean of 299.95 includes 11 sentinel values and old rows in cents.”

Conditions on groups: HAVING

WHERE filters rows, before the grouping. HAVING filters groups, after the aggregation. So HAVING can test an aggregation, and WHERE cannot.

Grace asks: “Which teams had at least 40 urgent tickets?”

SELECT team, COUNT(*) AS urgent_rows
FROM tickets
WHERE priority = 1          -- 1. keep the urgent rows (186 rows)
GROUP BY team               -- 2. make one group per team (5 groups)
HAVING COUNT(*) >= 40       -- 3. keep the groups with 40 or more (2 groups)
ORDER BY urgent_rows DESC;

The answer is payment (59) and delivery (49). The other teams have returns 39, warranty 21 and account 18; together 186. If you write WHERE COUNT(*) >= 40, SQL stops with an error: WHERE runs before the groups exist.

Count with a condition: CASE

Sometimes a question needs several counts from the same rows, each with its own condition. CASE gives a value that depends on a test:

CASE WHEN status = 'escalated' THEN ticket_id END

This gives ticket_id when the status is escalated, and NULL in all other rows. COUNT leaves NULL out, so COUNT(DISTINCT CASE WHEN status = 'escalated' THEN ticket_id END) counts the different tickets that have an escalated event.

On the outcomes table, one row is one outcome event in a ticket’s life:

Run SQL in your browser

Try it: events and tickets

Run it. You should see 1874 events for 1399 tickets, 228 escalated events and 225 escalated tickets. The difference of 3: three escalation events are logged twice, as you see in the next lesson. Change both 'escalated' to 'resolved' and run it again: 1533 events, 1396 tickets.

SQL query (you can change it)

SELECT
    COUNT(*) AS events,
    COUNT(DISTINCT ticket_id) AS tickets_with_events,
    COUNT(CASE WHEN status = 'escalated' THEN outcome_id END) AS escalated_events,
    COUNT(DISTINCT CASE WHEN status = 'escalated' THEN ticket_id END) AS escalated_tickets
FROM outcomes;

A ticket can have several events, so a count of events is not a count of tickets. For “how many tickets were escalated?”, count distinct ticket_id.

Guided practice: the team summary

Open a terminal in ~/projects/ticket-data.
Make the file queries/23-team-summary.sql with Mei’s query from the worked example.
Run it:
- macOS
- Windows
Terminal window
python3 q.py queries/23-team-summary.sql
Terminal window
python q.py queries/23-team-summary.sql
Check: you see 5 rows, and the account row has 0 in with_value.
Add up the ticket_rows column. Check: the sum is 1,417.
Add up the tickets column. Check: the sum is 1,403.
Add the line WHERE order_value <> -1 between FROM tickets and GROUP BY team. Before you run it, predict the ticket_rows of the account team.
Run the file again. Check: the account row is gone, and min_value is no longer −1.
Remove the line from step 6. Save the file.

Show the explanation of step 7

The account team has NULL in every row. NULL <> -1 is unknown, so WHERE drops all 188 rows, and the group does not exist any more. The result has 4 rows: 1,417 − 188 − 11 = 1,218 rows went in. A filter on one column can remove a whole group without a message. Count the groups before and after.

Run SQL in your browser

Your turn: rows per channel

The starting query gives 9 groups, because each spelling is its own group. Change it so that Email, EMAIL and email form one group, and chat joins chat. Sort the result from the largest group to the smallest. When it is correct, you see 6 rows, the first is email with 568 rows, and the check passes.

SQL query (you can change it)

-- One row per channel, written in a standard form.
-- Show the channel and the number of rows, largest first.
SELECT channel, COUNT(*) AS ticket_rows
FROM tickets
GROUP BY channel;

Show the example answer

SELECT LOWER(TRIM(channel)) AS channel_for_report, COUNT(*) AS ticket_rows
FROM tickets
GROUP BY LOWER(TRIM(channel))
ORDER BY ticket_rows DESC;

channel_for_report	ticket_rows
email	568
chat	421
web_form	200
phone	185
NULL	38
webform	5

The groups add up to 1,417. Six groups remain for four real channels: webform and web_form are the same channel, and NULL is “not recorded”. LOWER(TRIM()) does not fix a different spelling. Module 4 decides what to do with both.

Independent variation: one long message

Grace says: “Urgent tickets are long. Customers write more when it is serious.” Check her claim with the length of the first message, word_count.

Run SQL in your browser

Your turn: words per priority

Write one row per priority, sorted by priority. Give four columns: the priority, the number of rows, the mean word_count rounded to 1 decimal place, and the largest word_count. Then decide: is Grace right?

SQL query (you can change it)

-- For each priority: the number of rows, the mean word count
-- (rounded to 1 decimal place) and the largest word count.
-- Write your query here.

Show the example answer

SELECT
    priority,
    COUNT(*) AS ticket_rows,
    ROUND(AVG(word_count), 1) AS mean_words,
    MAX(word_count) AS max_words
FROM tickets
GROUP BY priority
ORDER BY priority;

priority	ticket_rows	mean_words	max_words
1	186	371.0	48210
2	526	109.1	435
3	705	106.4	553

Priority 1 has a mean of 371 words, more than three times the others. But MAX shows one message of 48,210 words: a pasted log file, in T-10361. Add WHERE word_count < 10000 and the priority 1 mean falls to 112.4, close to the others. So Grace is not right: one row made the difference. A mean needs the count, the minimum and the maximum next to it.

Common mistake

The mean order value includes sentinels and cents

Symptom: Tomás puts “mean order value: $299.95” in a slide about the model’s features. Omar, the product manager, says that a typical Larkfield order is about $100, and asks where the number comes from.

Cause: AVG(order_value) used every non-NULL value. 11 rows have −1, a sentinel for “unknown”. 25 rows from the old web form are in cents, up to 34,518. SQL gave no error, because the arithmetic is correct. The values are wrong.

Diagnose: print COUNT(column), MIN and MAX next to every AVG. A minimum of −1 for a price, or a maximum of 34,518 for a garden order, shows the problem at once. Then sort the rows by the column and read the extremes.

Fix: do not report the mean until the column is clean. Module 4 converts the cents rows, marks −1 as unknown, and keeps the original values. Until then, report the problem with its counts.

Consequence in AI work: a model that uses order_value learns from the wrong scale. Some preparation steps fill missing values with the mean, or scale values with the mean and the standard deviation. They carry the error into every row.

Check your understanding

The tickets table has 1,417 rows. COUNT(*) gives 1,417, COUNT(order_value) gives 1,229 and COUNT(DISTINCT ticket_id) gives 1,403. Explain each difference.

Show the answer

COUNT(*) counts every row. COUNT(order_value) leaves out the 188 rows where order_value is NULL: the account tickets, which have no order. COUNT(DISTINCT ticket_id) counts each ticket once: 14 tickets appear twice as duplicate rows from the export, so 1,417 − 14 = 1,403. To answer “how many tickets?”, use the distinct count.

Check your understanding

Predict: on the six-order table, what does AVG(COALESCE(order_value, 0)) give? (COALESCE(x, 0) gives 0 where x is NULL.) Is that a better mean?

Show the answer

COALESCE turns T-4’s NULL into 0, so there are 6 values with the same sum, 5149: 5149 ÷ 6 = 858.17. It is not better. T-4 is an account question with no order, and the query now counts it as an order of $0. On the real table, the same mistake moves the mean from 299.95 to 260.16. Missing has a meaning here; do not replace it with a number before you know what it means.

Check your understanding

What is the difference between WHERE and HAVING? Write a query that lists the customers with at least 30 distinct tickets.

Show the answer

WHERE filters rows before they are grouped. HAVING filters groups after the aggregation, so it can use COUNT, SUM and the others.

SELECT customer_id, COUNT(DISTINCT ticket_id) AS tickets
FROM tickets
GROUP BY customer_id
HAVING COUNT(DISTINCT ticket_id) >= 30
ORDER BY tickets DESC;

It returns 11 customers. The first is C-0226 with 46 tickets (47 rows, because one of its tickets is duplicated).

Recap

An aggregation turns many rows into one value: COUNT, SUM, AVG, MIN, MAX. With GROUP BY, you get one value per group. The group counts must add up to the rows that went in.
COUNT(*) counts rows, COUNT(column) counts non-NULL values, and COUNT(DISTINCT column) counts different values. “How many tickets?” is COUNT(DISTINCT ticket_id): 1,403.
The aggregations leave out NULL. That is right when NULL means “no value”, as for account tickets with no order.
SQL calculates correctly on wrong values: the sentinel −1 and the cents rows make AVG(order_value) 299.95. Print MIN and MAX next to every mean.
WHERE filters rows before grouping; HAVING filters groups after it. CASE gives a value per row, which lets one query count several conditions.

Next, you combine two tables with a join, and check that the join did not add or lose rows.

Next: Join tables