Group & Aggregate

Collapse many rows into one row for each group. Count, total, or average what is left.

What it does

Group & Aggregate joins rows that share the same value. You pick the columns to group by. You pick the numbers to compute. The result has one row for each group.

Before. Four coffee shop orders:

After. Group by store, with SUM of amount:

The drink column is gone. The output keeps only the group columns and the results.

Summarize your data

1. Type group in the Search transforms box, in the Pipeline panel.
2. Select Group & Aggregate in the results. The panel opens below the grid.
3. Open Group by (optional) and select one or more columns.
4. Click Add aggregation. One aggregation row appears.
5. Pick a function in the first dropdown of that row.
6. Open Select column… in the same row and pick a column.
7. Repeat steps 4 to 6 for each result you want.
8. Read Group & Aggregate Preview. It shows up to five result rows.
9. Click Apply.

The grid updates at once. The step appears in your pipeline, where you can edit or delete it later.

The functions you can pick

• COUNT: how many rows have a value.
• SUM: the total of a number column.
• AVG: the average of a number column.
• MIN and MAX: the smallest and the largest value.
• COUNT DISTINCT: how many different values.
• MEDIAN: the middle value of a number column.
• STRING_AGG: all values in one text, separated by commas.

Add a second aggregation row to get two results at the same time. An example is COUNT of drink and SUM of amount.

Tips

• Leave Group by (optional) empty for one row for the whole table. The panel shows a note about this.
• A column that you group by does not appear in the aggregation column list.
• Before you add the first row, the panel offers + All numeric columns. It adds a SUM row for each number column.
• Result columns get names like amount_sum or drink_count.
• This step drops every other column. Put it near the end of your pipeline.
• Click the small remove icon on an aggregation row to delete it. The icon appears when you have two rows or more.

For SQL users

The step runs as one GROUP BY query:

SELECT "store", SUM("amount") AS "amount_sum" FROM data GROUP BY "store"

Related Operations

• Pivot - Pivot table (rows to columns)
• Window Function - Rank, row number, lead, lag
• Smallest - Get nth smallest value as a column