WEBVTT

00:00:00.350 --> 00:00:01.331
Welcome back!

00:00:01.511 --> 00:00:02.685
Here's what we'll learn.

00:00:02.865 --> 00:00:06.022
Turn invoice rows into one sales summary per
city.

00:00:06.202 --> 00:00:09.978
Then filter individual rows with where and whole
groups with having.

00:00:10.158 --> 00:00:15.256
We will focus on building the query; result
formats and file exports have their own next

00:00:15.256 --> 00:00:15.662
lesson.

00:00:17.050 --> 00:00:18.842
Let's total sales by city.

00:00:19.022 --> 00:00:21.155
City is the alias from lesson four.

00:00:21.335 --> 00:00:24.151
Group by puts rows from the same city together.

00:00:24.331 --> 00:00:26.315
Sum adds their calculated amounts.

00:00:26.495 --> 00:00:30.079
We get one summary per city, not one result per
invoice row.

00:00:31.409 --> 00:00:33.095
Let's name the report columns.

00:00:33.275 --> 00:00:35.024
We just ran without aliases.

00:00:35.204 --> 00:00:40.324
Here, optional AS labels make the report easier
to read: sales and row count.

00:00:40.504 --> 00:00:43.981
Count measures invoice rows, not customers or
units sold.

00:00:44.161 --> 00:00:47.340
The database still holds all thirty-six original
rows.

00:00:48.750 --> 00:00:50.478
Let's filter rows and groups.

00:00:50.658 --> 00:00:54.242
Where keeps individual rows priced at least five
before grouping.

00:00:54.422 --> 00:00:58.091
Having then keeps cities whose remaining sales
reach one hundred.

00:00:58.271 --> 00:01:00.063
Florence and London qualify.

00:01:00.243 --> 00:01:02.057
Both thresholds are parameters.

00:01:03.450 --> 00:01:04.943
Thanks for following along!

00:01:05.123 --> 00:01:10.268
We turned detail rows into city summaries, then
separated filtering before and after

00:01:10.268 --> 00:01:10.819
grouping.

00:01:10.999 --> 00:01:12.749
Keep this report query ready.

00:01:12.929 --> 00:01:18.021
In the next lesson, we will choose how to fetch
its results in Python and export them to

00:01:18.021 --> 00:01:18.369
files.