Skip to content

Aggregate

The Aggregate component generates summary (aggregate) functions along with a group-by clause.

The Aggregate component groups together multiple input rows into a single output row. Input columns can be added to the groupings or have an aggregation applied to them.

Default output names are chosen by combining the source column name and the aggregation type. If they are not appropriate, consider using a Rename component immediately after the Aggregate component to choose more appropriate names.

Properties

Name = string

A human-readable name for the component.

Groupings = dual listbox

Select one or more columns from the source table that will form the groupings. The output flow will contain one row for each distinct combination of values within the grouping columns.

Aggregations = column editor

Select the input (source) column for the summary function.

Grouping Type = drop-down

Select one of the following methods for grouping rows:

  • GROUP_BY: Groups rows with the same group-by-item expressions and computes aggregate functions for the resulting group. A Snowflake GROUP_BY expression can be a column name, a number referencing a position in the SELECT list, or a general expression.
  • GROUP_BY_CUBE: The Snowflake GROUP_BY_CUBE clause is an extension of the GROUP_BY clause similar to GROUP_BY_ROLLUP. In addition to producing all the rows of a GROUP_BY_ROLLUP, GROUP_BY_CUBE adds all the "cross-tabulations" rows. Subtotal rows are rows that further aggregate, whose values are derived by computing the same aggregate functions that were used to produce the grouped rows.
  • GROUP_BY_GROUPING_SETS: A Snowflake GROUP_BY_GROUPING_SETS clause is a powerful extension of the GROUP_BY clause that allows computing multiple group-by clauses in a single statement. The group set is a set of dimension columns.
  • GROUP_BY_ROLLUP: A Snowflake GROUP_BY_ROLLUP clause is an extension of the GROUP_BY clause that produces subtotal rows (in addition to the grouped rows). Subtotal rows are rows that further aggregate, whose values are derived by computing the same aggregate functions that were used to produce the grouped rows.