This is an old revision of the document!
Table of Contents
AGGREGATE
Category: Transform / Basic
Description
This action calculates the aggregates for selected columns, grouped by another set of columns.
Available aggregations:
- Sum
- Count
- Count only distinct values
- Average
- Min
- Max
- Any
- Concatenate
Action settings
Setting | Description |
---|---|
Calculate | Select the column in which the values will be aggregated. |
Aggregation method | Select the aggregation method to be used on the values in the chosen column. |
Group by | Select the column(s) in which the grouping of values will drive the aggregation. |
You can aggregate more columns by the selected Group by columns by using the Aggregate by more columns option.
The output of this action is a dataset containing the "Group by" column, plus an additional column for each aggregation chosen.
Examples
Example 1: Find out the longest river on each continent.
Source table: The longest rivers in the world
River | Length(km) | Continent |
---|---|---|
Nile | 6650 | Africa |
Amazon | 6400 | South America |
Yangtze | 6300 | Asia |
Mississippi | 6275 | North America |
Yenisei | 5539 | Asia |
Yellow River | 5464 | Asia |
Ob | 5410 | Asia |
Paraná | 4880 | South America |
Action parameters:
Calculate: Length(km), Aggregation: Max
Group by: Continent
Result table:
Continent | Max of Length(km) |
---|---|
Africa | 6650 |
Asia | 6300 |
North America | 6275 |
South America | 6400 |
Example 2: Find out the longest river, and the number of rivers, on each continent.
Source table: Same source table as Example 1.
Action parameters:
Calculate: Length(km), Aggregation: Max
Calculate: River, Aggregation: Count
Group by: Continent
Result table:
Continent | Max of Length(km) | Count of River |
---|---|---|
Africa | 6650 | 1 |
Asia | 6300 | 3 |
North America | 6275 | 1 |
South America | 6400 | 2 |
Community examples
- Compare two Excel sheets with EasyMorph (Project; Module: Main; Group: Compare column names; Table: Actual columns list;
Action position: 3) - Generate CommaSeparated Fieldlist (Project; Module: Main; Group: Tab 1; Table: Table 1; Action position: 4)
- Which technique for grouping many columns of data together (i.e. 50+ columns) (Project; Module: Main; Group: Tab 1;
Table: Aggregate; Action position: 2) - Aggregate for +500 columns (Project; Module: Main; Group: Order preserved; Table: Book1.xlsx(2); Action position: 3)
- Collapsing multiple rows into one (Project; Module: Main; Group: Group 1; Table: Method 2; Action position: 2)
Shortcuts
Fast ways to create the action:
- Right-click on a column heading, and select "Aggregate", then select the aggregation method from the submenu.
- Click the drop-down arrow in a column heading and select "Aggregate", then select the aggregation method.