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.
| region | SUM(amount) | COUNT(*) | COUNT(amount) |
|---|---|---|---|
| North, from 120 and 80 | 200 | 2 | 2 |
| South, from 160 and a null | 160 | 2 | 1 |
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
- 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. - Compare
invoicesandpriced. South has 7 invoices but 6 prices: that's INV-1014, still unpriced. A sum would never tell you. - Under Actions, select Add. The new row is a Group by: set its Column to
customer. - Read the preview. It should have four rows, and
net_valuefor 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.WHEREruns before grouping, andHAVINGruns 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 regionEvery 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.