Two tills, one answer
The made-up shop sells online and at the till. Both exports are clean now. The owner asks one simple thing: "How many sales did we make in total?"
You cannot answer from either file alone. You must put the two together. There are two ways to do that, and picking the wrong one gives a wrong answer.
Key words
| Word | What it means | Example |
|---|---|---|
| Requirement | What the finished data must be able to do or show. | One row per sale |
| Dataset | A set of data that is ready to be analysed, with a clear shape. | One table of every sale |
| Stack | Put the rows of one table under the rows of another. The columns match. | Web sales above till sales |
| Join | Add columns to a row by matching a key in another table. | Add a category to each sale |
| Lookup table | A small table that holds extra facts about one kind of thing. | Each item and its category |
| Key | The column used to match rows between two tables. | item |
| Null | A cell with no value. | A sale with no category |
| Consolidate | Bring data from many places into one. | Two sales files into one |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can choose between stacking and joining, check a blend, and build a finished dataset for a requirement.
Starter: last lesson
Have a go
Starter: last lesson
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Two ways to blend
Stack when two tables hold the same kind of rows with the same columns. A web sale and a till sale are both sales. Stack them to get all the sales.
Join when one table has extra facts about the rows of another. A sale names an item, and a lookup table says the item's category. Join them to add a column.
Worked example: which technique?
| Technique | Use it when | The shop example | Result |
|---|---|---|---|
| Stack | Same columns, different rows | Web sales and till sales | More rows: 3 and 3 give 6 |
| Join | Same rows, extra columns | Sales and the products lookup | Same rows, one more column |
The cells students fill in are left blank in this preview.
Try it: stack the two sources
select 'web' as source, ref, sold_on, item, price from web_sales
union all
select 'till', ref, sold_on, item, price from till_sales
order by sold_on, ref;
-- A source column tells you where each row came from.Try it: join a category on
with sales as (
select 'web' as source, ref, sold_on, item, price from web_sales
union all
select 'till', ref, sold_on, item, price from till_sales
)
select s.source, s.ref, s.item, p.category
from sales s
left join products p on s.item = p.item
order by s.ref;
-- Which sale has no category? Why?