This gem runs in .
Overview
Use the Aggregate gem to group rows and calculate summary values for each group. This is useful for reporting, customer analysis, financial summaries, and KPI calculations. 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.
GROUP BY statement.
Parameters
| Parameter | Description |
|---|---|
| Group By | Select the column or columns used to group rows into categories. Each unique combination becomes one output row. |
| Expressions | Define the aggregation calculations to perform for each group. Each aggregation creates a new output column. |
| Having Conditions | Filter grouped results after the aggregation step completes. Similar to a SQL HAVING clause. |
Common examples
| Goal | Group By | Expression |
|---|---|---|
| Count orders per customer | customer_id | count(order_id) |
| Calculate total revenue by region | region | sum(order_amount) |
| Find average order value by sales rep | sales_rep | avg(order_amount) |
| Get the latest transaction date per account | account_id | max(transaction_date) |
| Count distinct products sold by store | store_id | count_distinct(product_id) |
How Aggregate works
The Aggregate gem processes data in three main steps:- Group rows based on the selected Group By columns.
- Calculate aggregation expressions for each group.
- Optionally filter grouped results using Having Conditions.
- A Filter gem can remove individual rows before grouping.
- A Having condition can remove grouped results after calculations are complete.
Example
Suppose you have a dataset of orders that includes thecustomer_id for each customer. You can use the Aggregate gem to analyze customer purchasing behavior.
Example configuration
| Setting | Value |
|---|---|
| Group By | customer_id |
| Expressions | count(order_id) as order_count |
| Expressions | sum(order_amount) as total_spent |
| Having Conditions | count(order_id) < 3 |
Result
This configuration will:- Group all rows by
customer_id - Count the number of orders for each customer
- Calculate the total amount spent by each customer
- Return only customers who placed fewer than three orders
Common issues
Unexpected duplicate groups
Check for:- Leading or trailing spaces in grouped columns
- Differences in letter casing such as
USversusus - Null values creating separate groups
Incorrect counts
If counts appear too high:- Verify whether duplicate rows exist before aggregation
- Confirm whether you should use
count()orcount_distinct()
Having condition does not work as expected
Remember that Having conditions 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
| Tool or Platform | Similar Concept |
|---|---|
| SQL | GROUP BY with aggregation functions |
| Alteryx | Summarize tool |
| Pandas | groupby() with aggregate functions |
| PySpark | groupBy().agg() |
| Excel | PivotTables and grouped summaries |