Group By

Group By

Type: group-by  ·  Package: DataTable Activities v1.0.5  ·  Output: DataTable

Groups rows by the values of a column and computes one or more aggregates per group, returning a summary table. It is the equivalent of a pivot table: “total sales per customer”, “number of orders per status”, “average per region”.

How it works

Gathers the rows that share the same value in the grouping column and, for each group, computes the aggregations you define. The result is a new table with one row per group: the grouping column plus one column per aggregate.

• Grouping values are compared as text and case-insensitively: "ACME" and "acme" fall in the same group (which keeps the spelling of the first row). Empty cells form a group with an empty key.
• Groups come out in the order in which each value first appears.
• Each aggregation is a { column, operation, alias } object:
  • operation: sum, count, avg, min or max (default sum). count counts the rows in the group.
  • alias: name of the resulting column; if omitted, the column name is used.
  • As in Aggregate Column, empty or non-numeric cells are skipped and a group with no numeric values gives 0.
• Items in aggregations that are not objects are ignored.

Required parameters

Parameter	Type	Default	Description
table	DataTable	—	The DataTable to group.
column	string	—	Column to group by.
aggregations	List	—	List of {column, operation, alias} objects. operation: sum · count · avg · min · max. alias is the name of the resulting column.

Optional parameters

None.

Output

Returns a DataTable with one row per group. Columns:

• the grouping column, with the same name, as text (string);
• one numeric column (double) per aggregate, named after its alias.

An input table with no rows gives a summary table with no rows.

Common errors

Message	Cause and fix
'table' must be a DataTable.	table does not evaluate to a DataTable.
'column' is required.	The grouping column is missing.
'aggregations' must be an array.	aggregations is not a list of objects.
Unsupported operation '…'. Use: sum, count, avg, min, max.	An aggregation has an invalid operation.
Column '…' does not belong to table ….	The grouping column or an aggregation’s column does not exist (.NET message).
A column named '…' already belongs to this DataTable.	Two aggregations share the same alias, or an alias matches the grouping column. Use distinct aliases.

Example

Total and number of invoices per customer. In the Designer, aggregations are defined with a visual editor: for each one you choose the column, the operation and an alias (name of the resulting column).

Group By
  table  = invoices
  column = "Customer"
  aggregations:
     Total · sum   → alias "CustomerTotal"
     Total · count → alias "InvoiceCount"
  → output: summaryByCustomer

Sort DataTable   table = summaryByCustomer   column = "CustomerTotal"   direction = desc   → output: ranking

The result has the columns Customer, CustomerTotal and InvoiceCount, with one row per customer.

Related activities

• Aggregate Column — a global aggregate (no grouping).
• Sort DataTable — sort the resulting summary.
• Filter DataTable — narrow down before grouping.