Free lesson
From rows to answers
Turn a pile of orders into the numbers the owner asks for, then catch a number that lies.
In this lesson you will
- Turn many rows into one number
- Make one pile per group with GROUP BY
- Catch a duplicate that makes a total lie
Words you'll meet
- Aggregate
- Squash many rows into one value, such as a count or a sum.
- Grain
- What one row of a table stands for.
- Duplicate
- The same row recorded more than once.
The lesson, step by step
Each step teaches one idea, then hands you a real database to try it on. The exercises run in your browser.
Step 1 of 8
Make a pile
The owner stops by your desk:
How many drinks did Boston sell this week?
Nobody asks to see eight rows. They ask for a number.
Your turn: Click every Boston order and watch your pile add up. Then press Check.
Step 2 of 8
COUNT
Squashing many rows into one number is called aggregating.
COUNT(*)counts the rows in the pile:SELECT COUNT(*) FROM orders WHERE city = 'Boston'
Leave out the
WHEREand the pile is the whole table.COUNT(*)counts rows.COUNT(customer)counts rows where customer is filled in.COUNT(DISTINCT customer)counts different customers.On this table those can give three different answers. "How many customers do we have?" and "how many orders?" are different questions, and a lot of wrong reports come from mixing them up.
Your turn: Count all the orders in the table.
Step 3 of 8
SUM
And how much money did we take this week?
SUMadds up a column, or anything you can work out for each row. Its cousins areAVG,MINandMAX.Your turn: Work out total revenue: the sum of quantity times price across all orders.
Step 4 of 8
One pile per city
Good. Now break that down by shop.
You could make a pile for Austin, then Boston, then Denver.
GROUP BYmakes all the piles at once and gives you one number for each:SELECT item, COUNT(*) FROM orders GROUP BY item
Read it as: "make a pile of rows for each item, then count each pile."
There is one rule to remember. Every column you
SELECTmust either appear inGROUP BYor sit inside a function likeCOUNTorSUM.The reason: a pile has many rows but gets one line of output. If you ask for
customerwhile grouping by city, the database cannot know which customer in the pile you mean, and it will refuse.Your turn: Show each city and how many orders it had.
Step 5 of 8
Grain
Notice what happened.
ordershas one row per order. Your result had one row per city.What one row stands for is called the grain of a table. Knowing the grain of every table you touch is the most useful habit in data engineering. Most wrong numbers come from mixing grains up.
Grains you will meet: one row per order, one row per item within an order, one row per customer per day.
Before writing a query, say the grain of the table you are reading and the grain you want out. If an order's delivery fee is repeated on every item row and you add up that column, you have charged the fee several times. Saying the grain out loud catches that.
Your turn: What is the grain of: SELECT customer, SUM(quantity) FROM orders GROUP BY customer
Step 6 of 8
The scoreboard
Perfect. Put the money per shop on a chart for the board meeting, best shop first.
You can group, add up and sort in one query.
Your turn: Show each city and its total revenue (sum of quantity times price), highest revenue first.
Step 7 of 8
A number that lies
The next day the Denver manager phones, annoyed:
Your chart says we took $22. Our till says $13. Which is it?
Your query has not changed. Something in the data has. Here are Denver's orders.
Duplicates come from ordinary events: a file exported twice, a job retried after a network hiccup, someone uploading the same spreadsheet again.
Nothing reports an error. A total just gets bigger. A later lesson is entirely about building loads that stay correct when they are run twice.
Your turn: Find the problem. Click an order that should not be there.
Step 8 of 8
Challenge
You found that one by eye because Denver has three rows. In a real table you would need the database to find it.
You already know enough: make one pile per
order_idand count each pile. A healthy order has a count of 1.Your turn: Show each order_id and how many rows it has, biggest count first.
Reading is the easy part.
Every step above has an exercise. Do it for real, free, no account needed.
Start the lesson