Three sheets, one report
Each shop keeps its sales on its own worksheet. The owner wants one list. Later, the owner also wants to see the live price list without copying it.
Three ideas cover these jobs: consolidate, merge and link.
Key words
| Word | What it means |
|---|---|
| Consolidation | combining separate worksheets into one worksheet |
| Merging | combining several datasets into one dataset |
| Linking | pointing at data that stays in its own source |
| Dataset | a collection of related data about one subject |
| View | a saved search that shows fresh data each time |
| Worksheet | one page of cells in a spreadsheet file |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can tell consolidation, merging and linking apart and use each one.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Consolidation stacks sheets into one
Consolidation combines separate worksheets of the same shape into one worksheet. Add a column to say where each row came from.
The sheets must have the same columns, in the same meaning.
Try it: consolidate three sheets
select 'Leeds' as shop, * from sheet_leeds
union all
select 'York' as shop, * from sheet_york
union all
select 'Hull' as shop, * from sheet_hull
order by shop, product;
-- Try it: group the result by product and add up the units.
Merging combines different datasets
Merging combines several datasets into one dataset. Unlike consolidation, the datasets may hold different things, such as sales and prices.
You usually merge with a join on a shared key. The result is one new dataset.
Try it: merge sales with prices
create table all_sales as
select 'Leeds' as shop, * from sheet_leeds
union all select 'York', * from sheet_york
union all select 'Hull', * from sheet_hull;
select a.shop, a.product, a.units * p.price_pence as pence
from all_sales a
join source_prices p on p.product = a.product
order by a.shop, a.product;
Check
Have a go
Check
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.