---
title: "GROUP BY and PivotTables explained | Oplema"
description: "Learn how GROUP BY and PivotTables collapse rows into totals and counts, why COUNT(*) and COUNT(column) differ and how to check the grouped result."
url: "https://oplema.com/learn/data-preparation/summarize"
updated: "2026-11-04"
---

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
| 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](https://oplema.com/learn/data-preparation/what-is-data-prep)'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](https://oplema.com/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.

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](https://oplema.com/docs/strata/transform/summarize), and [how each step changes a table](https://oplema.com/learn/data-preparation/how-tables-change) 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](https://oplema.com/learn/business-intelligence/aggregation) 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.
