Aggregate gem for Data Analysis

​

Overview

By default, the Aggregate gem provides a guided interface for defining grouping columns, aggregation functions, and aggregate filters. If you need more complex expressions, you can switch to Advanced mode to edit the underlying SQL configuration directly. The Aggregate gem transforms many input rows into fewer output rows. It groups records that share the same values and calculates summary statistics for each group. Common use cases include:

• Counting orders per customer.
• Calculating total sales by region.
• Finding average transaction values.
• Summarizing records by category or date.
• Creating grouped metrics for dashboards and downstream analysis.

The Aggregate gem works similarly to a SQL GROUP BY statement.

​

Modes

The Aggregate gem supports two editing modes:

​

Simple mode

Simple mode organizes the Aggregate gem into three collapsible sections.

​

Supported aggregation functions

​

Complex expressions

Simple mode supports standard grouping columns and aggregation expressions such as sum(order_amount) or avg(age). If an existing Aggregate gem contains more advanced expressions, Simple mode displays a message indicating that the configuration is too complex to edit in Simple mode. For example, Simple mode does note support calculated grouping expressions such as sum(a) + sum(b). In these cases, you have two options:

• Switch to Advanced Mode lets you edit the existing configuration in Advanced Mode.
• Reset and use Default Mode clears the grouping and aggregation configurations so that you can start over.

​

How the Aggregate gem works

The Aggregate gem processes data in three main steps:

1. Group rows based on the selected Group By columns.
2. Calculate aggregation expressions for each group.
3. Optionally filter grouped results using Having Conditions.

​

Example grouping

After applying employee_count >= 2, Sales is removed. Unlike a Filter gem, Having Conditions are evaluated after aggregation is complete. For example:

• A Filter gem can remove individual rows before grouping.
• A Having condition can remove grouped results after calculations are complete.

​

Common use cases

​

Example

Suppose you have a dataset of orders that includes heathcare users with the following columns: You can use the Aggregate gem to summarize users by persona and calculate average demographic metrics for each segment.

​

Example configuration

​

Result

This configuration:

• Groups all rows by persona.
• Calculates the average household income, age, and number of dependents for each persona.
• Returns the persona value for each group using any_value.

Because persona is also the grouping column, every row within a group has the same persona value. In cases like this, any_value returns that shared value and includes it in the output.

​

Work with grouping and aggregation rows

Both the Grouping and Aggregations sections support:

• Searching for existing rows.
• Adding rows with +.
• Deleting rows.
• Dragging rows to reorder them.

Hovering over a row highlights the corresponding source column in the input preview and the generated column in the output preview. Clicking a row keeps that highlight active until you select another row.

​

Common issues

​

Unexpected duplicate groups

Check for:

• Leading or trailing spaces in grouped columns.
• Differences in letter casing such as US versus us.
• Null values creating separate groups.

You may need to clean or standardize values before aggregation.

​

Incorrect counts

If counts appear too high:

• Verify whether duplicate rows exist before aggregation.
• Confirm whether you should use count() or count_distinct().

​

Aggregate Filters/Having Conditions does not work as expected

Aggregate Filters (Simple mode) and Having Conditions (Advanced mode) filter grouped results after aggregation. If you need to filter raw rows before grouping, use a Filter gem earlier in the pipeline.

​

Similar tools and concepts