Shape · Summarize and reshape

Aggregate

Collapses rows into one row per group and works out values for each: sum, count, average, median, mode, percentiles, longest text and more, cash-flow values such as NPV or IRR, and for a geometry the dissolved shape, extent or centroid. With no group, you get one grand-total row.

Also known as: Summarize, group by

How it works

Pick the Group by columns, or leave them empty for one row over the whole table. Under Aggregations, click Add aggregation, pick one or more fields, then the operation. The operation menu lists what the chosen fields' types can take; the rest are shown last, disabled. One operation over several fields gives one column per field, named like sum_amount unless you rename it under Output columns; with one field, type the alias. The operations are Sum, Count, Average, Min, Max, Median, Std dev (sample standard deviation), Count distinct, First, Last, Concatenate, Mode (ties give the smallest value), Percentile (set the percent; interpolated, named like p25_amount) and, for text, Shortest and Longest. A number field also offers the cash-flow operations NPV, XNPV, IRR, XIRR, MIRR, MXIRR and FVSCHEDULE, with their Rate, Period or Date and other settings on the row; an IRR that does not converge warns per group. A geometry field offers Union (dissolve), Extent, Centroid, Convex hull and Collect, so shapes and totals come out of one group-by. Count offers All rows as its field; every other operation needs one. Concatenate joins the values with the Separator, , by default; First and Last take an Order by column and otherwise use the order rows arrive. A blank alias becomes sum_amount or count, and an alias already used gets a _2. The output is sorted by the group columns with nulls first.

Inputs and outputs

Settings

{
  groupBy: string[]
  aggregations: [{ column: string, func: "SUM"|"COUNT"|"AVG"|"MIN"|"MAX"|"COUNT_DISTINCT"|"MEDIAN"|"STDDEV"|"FIRST"|"LAST"|"STRING_AGG"|"MODE"|"PERCENTILE"|"SHORTEST"|"LONGEST"|"UNION"|"EXTENT"|"CENTROID"|"CONVEX_HULL"|"COLLECT" (the last five on a GEOMETRY column), alias: string, separator?: string (STRING_AGG), orderColumn?: string (FIRST/LAST), percentile?: number 0-100 (PERCENTILE, default 50) }]
}

Example

You want one line per region with its revenue, order count and number of distinct customers. Group by region and add Sum of amount as revenue, Count of All rows as orders and Count distinct of customer_id as customers. Add Union (dissolve) of geom to the same node and each region also gets its outline, ready for the Map tab.

More in Summarize and reshape

• Group Aggregate — Works out a group total and writes it onto every row instead of collapsing them, as the value, a ratio or a percent. For example, each sale as a share of its region's total.
• Running Aggregations — Adds running totals that build up row by row: sum, average, median, min, max, count or standard deviation, optionally restarting per group. For example, year-to-date sales.
• Pivot — Turns the values of one column into new columns, such as one row per store with a column per month. The reverse of Unpivot.
• Unpivot — Turns chosen columns into rows of name and value pairs. For example, Jan, Feb and Mar columns become month and sales rows.
• Transpose — Swaps rows and columns, so every column becomes a row. Best for small, wide summary tables.
• Split to Rows — Turns a cell holding several delimited values into one row per value. For example, 'red;green;blue' becomes three rows.
• Split to Columns — Breaks one text column into several columns at a delimiter, such as 'London, UK' into city and country.
• Explode — Turns each item of a list column into its own row. For comma-separated text, use Split to Rows instead.