Lesson 12 of 15 · Lab

GROUP BY: grouping and summarizing rows

Grouping collapses rows into one row per group, with a total or count for each. In SQL that's GROUP BY, and in Excel it's a PivotTable. One row now stands for a group, and you can't get the detail back from the result.

Four orders grouped by region, one with no amount
regionSUM(amount)COUNT(*)COUNT(amount)
North, from 120 and 8020022
South, from 160 and a null16021

COUNT(*) counts rows, and COUNT(amount) counts values that aren't null. A gap between them means a total is missing something.

You'll group fictional Vandelay Coffee Supply's 13 finished invoices, one of them unpriced.

If you group by customer as well as region, what happens to the regions' totals?

Nothing. Each region splits into its customers, and the customers add back to the same figures.

Split a summary by a second column

  1. This live flow in Strata, Oplema's browser app for data prep, opens on Summarize, with one Group by action for region. The preview has two rows, one per region.
  2. Compare invoices and priced. South has 7 invoices but 6 prices: that's INV-1014, still unpriced. A sum would never tell you.
  3. Under Actions, select Add. The new row is a Group by: set its Column to customer.
  4. Read the preview. It should have four rows, and net_value for each region should add back to what you saw in step 1.

Swipe sideways to see the canvas, or open this page on a larger screen to work with it.

How the regions should split

North is Starhopper €3,888 and Dunder Mifflin €1,690. South is Initech €3,000 and Wonka €2,900. The regions stay at €5,578 and €5,900.

Group more finely and the total has to hold

Before you trust a summary, check that it still adds back. If it doesn't, a row is missing or doubled. The name matters too, because net_value says what the number is, and anyone reading the output table will reuse your word. See the Summarize reference, and how each step changes a table on why grouping can't be undone.

Here is the same answer as a reader would see it, with two bars, a €322 gap, and nothing yet about when the gap opened.

Common grouping mistakes

  • Averaging averages. A customer with two invoices would count the same as one with four. Go back to the rows, as the aggregation lesson shows.
  • Summing identifiers. A column of digits can add up to a meaningless number.
  • Filtering groups with WHERE. WHERE runs before grouping, and HAVING runs after.

GROUP BY and PivotTables

SELECT region, SUM(net_eur) AS net_value,
       COUNT(*) AS invoices, COUNT(net_eur) AS priced
FROM invoices
GROUP BY region

Every selected column must be grouped or aggregated, or most databases return an error. In Excel, a PivotTable does this with no formula, and SUMIFS and COUNTIFS give one group at a time. COUNTA counts cells that aren't empty, and COUNT counts only numbers.