Grouping and Aggregating Data: A Practical Guide

"What's the total revenue by region?" "How many orders per product category?" "What's the average ticket price by venue?" These questions share the same structure: take a column, group rows by its values, and compute something for each group.

The Group & Aggregate operation handles this directly. Select your grouping columns, pick your aggregate functions, and apply. No SQL needed, though the generated SQL is visible in the pipeline card if you want to verify it.

Basic example: total revenue by region

Suppose you have an orders table with columns: order_id, region, category, revenue.

1. Click the green + in the Pipeline panel and select Group & Aggregate from the Aggregate group.
2. Set Group by columns to "region".
3. Add an aggregate: column "revenue", function SUM.
4. Apply.

The result collapses your detail rows into one row per region:

Multiple grouping columns

You can group by more than one column. Setting group columns to "region" and "category" produces one row for each unique region-category pair.

Multiple aggregates in one step

Each Group & Aggregate step can include several aggregations. For example, you might want SUM of revenue, COUNT of orders, and AVG order value all at once:

• Aggregate 1: revenue, SUM
• Aggregate 2: order_id, COUNT
• Aggregate 3: revenue, AVG

All three appear as columns in the output. This avoids chaining multiple pipeline steps for what is logically a single summary.

The output names are generated for you as <column>_<function>, so those three become revenue_sum, order_id_count, and revenue_avg. There's no alias box in the panel. If you'd rather ship a column called avg_order_value, add a Rename Columns step from the Columns group afterwards.

Useful aggregate functions

Beyond SUM and COUNT, there are several functions worth knowing:

• COUNT_DISTINCT counts unique values. Use it for questions like "how many distinct customers per region?"
• MEDIAN returns the middle value, which is more robust than AVG when your data has outliers.
• STRING_AGG concatenates text values with a comma separator. Handy for collapsing a list of product names or tags into a single cell.
• MIN / MAX return the smallest and largest values. Useful for date ranges: MIN(order_date) and MAX(order_date) give you the first and last order per group.

Whole-table aggregates

If you leave the group-by columns empty, the aggregation runs across the entire table and returns a single row. This is useful for computing totals or overall statistics:

The numbers tie out: the four regional subtotals above (142,500 + 87,300 + 63,200 + 51,800) come to 344,800, and 344,800 spread over 1,247 orders is an average of 276.50. Worth checking, because a grand total that doesn't match the sum of your groups usually means NULLs in the grouping column.

Need HAVING? Use SQL

Sooner or later you want "regions with more than 80,000 in revenue", which in SQL is a HAVING clause. The Group & Aggregate panel has no HAVING field, and there are two ways around that.

The easy way: group first, then filter. Once the step has run, revenue_sum is an ordinary column, so a plain Filter step with revenue_sum > 80000 gives you exactly what HAVING would. Two steps, no SQL, and each is independently editable.

The one-step way: click the green + and select SQL Query from the Advanced group:

SELECT "region", SUM("revenue") AS revenue_sum
FROM "sales_2026"
GROUP BY "region" HAVING SUM("revenue") > 80000

Against the region table above, that keeps North (142,500) and South (87,300) and drops West (63,200) and East (51,800).

Note the source table. In a fresh pipeline it's the file's table name; if you already have steps above this one, use pipeline_output instead so you're querying their result rather than the raw file.

Which to pick is mostly taste. I reach for group-then-filter when I'm still exploring, because I can tweak the threshold without touching SQL, and for the single SQL step when the pipeline is settled and I want it to read as one intention.

Group & Aggregate vs. Pivot vs. Window

ExploreMyData has three operations that do aggregation. Here is when to use each:

• Group & Aggregate: You want a summary table with one row per group and one or more aggregate columns. This is the most direct tool for "total X by Y" questions.
• Pivot: You want the grouped values spread across columns instead of rows. Pivot is better when the grouping column has a small number of distinct values and you want them as column headers.
• Window Function: You want the aggregate value attached to every detail row instead of collapsing the data. Use this for percentage-of-total calculations or comparing each row to its group average.

For the window function side of that comparison, see Window Functions Without Writing SQL. For full documentation, see the Group & Aggregate reference.

Start aggregating your data →