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

  1. Summarize opens first. A Group by on bike_code gives one row per code. hires is a Count action, and bikes, gross and net are each a Sum.
  2. Add up hires: 52 + 12 + 14 + 15 = 93.
  3. Add an action: the Max of bikes. Select its Output field, select all (Ctrl+A or Cmd+A) and type largest in place of the prefilled max.
  4. Read largest. No EBK, KID or TDM hire is more than 3 bikes. One CTY hire is 26.
  5. In the palette, select Transform and drag Count Dataset below Summarize.
  6. Drag from Formula's right dot to Count Dataset's left dot. One output can feed two blocks.
  7. Select Count Dataset. rows reads 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
Harbour, September 14 to 20, finished hires
CodeHiresBikesGrossNet
CTY52101€2,470.00€1,976.00
EBK1216€606.00€484.80
KID1420€274.00€219.20
TDM1522€743.50€594.80
All93159€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. CTY and Cty would 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 check

In 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.