Lesson 8 of 10 · Lab
Total rows by group and prove the total is right
Summarize groups rows and calculates a total or count for each group. The groups have to add back up to the whole, and a row count taken outside the summary proves they do.
One row per bike code is what Marit's report shows. Each row stands for many hires, so a hire left out or counted twice can't be seen unless you check.
This live flow in Strata, Oplema's browser app for data prep, holds the week's 93 Harbour hires at fictional Kramerica Bikes and your Formula from the last lesson.
Add up every code's hires. Will you get 93?
Yes. Every row lands in exactly one group, so the groups add back to what came in. What you're proving is that 93 came in, the same 93 your filter kept.
Total by bike code, then count the rows
- Summarize opens first. A Group by on
bike_codegives one row per code.hiresis a Count action, andbikes,grossandnetare each a Sum. - Add up
hires: 52 + 12 + 14 + 15 = 93. - Add an action: the Max of
bikes. Select its Output field, select all (Ctrl+A or Cmd+A) and typelargestin place of the prefilledmax. - Read
largest. No EBK, KID or TDM hire is more than 3 bikes. One CTY hire is 26. - In the palette, select Transform and drag Count Dataset below Summarize.
- Drag from Formula's right dot to Count Dataset's left dot. One output can feed two blocks.
- Select Count Dataset.
rowsreads 93, the same as the hires in Summarize.
Swipe sideways to see the canvas, or open this page on a larger screen to work with it.
In an expression, press Esc to apply it and move on (press it twice if a suggestion list is open).
The totals you should see
| Code | Hires | Bikes | Gross | Net |
|---|---|---|---|---|
| CTY | 52 | 101 | €2,470.00 | €1,976.00 |
| EBK | 12 | 16 | €606.00 | €484.80 |
| KID | 14 | 20 | €274.00 | €219.20 |
| TDM | 15 | 22 | €743.50 | €594.80 |
| All | 93 | 159 | €4,093.50 | €3,274.80 |
Count Dataset says 93, the same as the hires column.
One hire stands out, and it isn't wrong
The 26-bike hire is one CTY booking on September 18, worth €1,001.00. That's 26 of the week's 159 bikes and nearly a quarter of its €4,093.50 gross. Nothing in these columns says who booked it.
It's real, so it stays, and the report should say it's there. It's also why Harbour looked so busy that Friday, which is what Tomas keeps telling everyone. A later course comes back to it.
See the Summarize and Count Dataset references. For later: Grouping and summarizing rows explains what a group means.
Common totaling mistakes
- Proving a total with itself. Adding up the groups checks the arithmetic but can't find missing rows. Count the rows separately, as Count Dataset does.
- Grouping on uncleaned text.
CTYandCtywould be two groups, which is why the text was cleaned first. - Removing a row because it's big. An outlier needs a reason to go. This one is real, so report it.
Checking a grouped total in SQL and Excel
SELECT bike_code, count(*) AS hires, sum(bikes) AS bikes,
sum(amount_eur) AS gross, sum(net_eur) AS net,
max(bikes) AS largest
FROM week
GROUP BY bike_code;
SELECT count(*) FROM week; -- the checkIn Excel, a PivotTable with bike code in Rows and the sums in Values does the same. Check its Grand Total for hires against =ROWS() on the data.