Which snack makes the most money?
The Oakfield Youth Club cafe is made up. It sells tea, biscuits and juice. The manager wants the total taken, the most expensive item and the best seller.
She does not want to add up 200 lines by hand. Then she finds the same person on the sign-up list twice. This lesson is about sums, tidying and finding patterns.
By the end of this lesson
Note
I can use calculations and data cleansing to get reliable answers from a table.
Key words
| Word | What it means |
|---|---|
| Calculation | a sum the table does for you, such as a total |
| Data cleansing | finding and fixing or removing bad data |
| Duplicate | a record that appears more than once |
| Data mining | searching large amounts of data for patterns |
| Pattern | something that happens again and again |
The cells students fill in are left blank in this preview.
Starter: what you already know
Have a go
Starter: what you already know
4 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
A query can add up for you
A calculation works out a number from many records. SUM gives the total. AVG gives the average. MAX gives the largest. MIN gives the smallest. COUNT counts the records.
Add GROUP BY to do the sum once for each group, such as each item.
Try it: totals and averages
-- All figures are in pence. quantity is how many were sold.
SELECT COUNT(*) AS sales,
SUM(price_pence * quantity) AS total_pence,
ROUND(AVG(price_pence), 1) AS average_price,
MAX(price_pence) AS dearest,
MIN(price_pence) AS cheapest
FROM cafe_sales;
-- One total for each item.
SELECT item, SUM(quantity) AS sold, SUM(price_pence * quantity) AS pence_taken
FROM cafe_sales GROUP BY item ORDER BY pence_taken DESC;
Check
Have a go
Check
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.