Summarizing Sales Data by Region and Product Category

You have a CSV export of order data. Management wants a breakdown: total quantity sold and total revenue, grouped by region and product category. Cancelled orders should be excluded. The output needs to land in an Excel file.

This is a five-minute job in ExploreMyData. Here's the exact pipeline.

Step 1: Import the CSV

Drop your file onto the app. The CSV has these columns:

ExploreMyData auto-detects column types. quantity and unit_price will be recognized as numeric.

Step 2: Filter out cancelled orders

Click the green + in the Pipeline panel and select Filter from the Filter & Sort group. Leave the Action toggle on "Keep matching rows" and set one condition: column status, operator "is not", value cancelled. That drops cancelled rows before anything gets totalled. In the example above, order 1003 goes. Four rows are left.

Step 3: Build the revenue column with Math

Group & Aggregate sums a column. It cannot sum an expression you type into it, so SUM(quantity * unit_price) is not something you can ask it for. Make the column first.

Click the green + in the Pipeline panel and select Math from the Transform group. It gives you one free-text formula box. Type:

"quantity" * "unit_price"

Column names are typed in double quotes. Set "Apply results into" to New Column and name it revenue; blank leaves you with a column called result. Order 1001 now reads 449.97, order 1002 reads 199.95, and so on. If your export already carries a revenue column, skip this step and aggregate that one instead.

Step 4: Group & Aggregate

This is the core step. Click the green + in the Pipeline panel and select Group & Aggregate from the Aggregate group, then fill in:

• Group by: region, category
• Aggregation 1: function SUM, column quantity
• Aggregation 2: function SUM, column revenue

Each aggregation row asks for the function first and the column second. There is no field for naming the output: the alias is always the column name plus the lowercased function, so you get quantity_sum and revenue_sum. Anything you put in Group by disappears from the aggregation column list, which is a useful hint that you have not double-counted a column.

The result is one row per region-category combination:

Four groups, four rows. Each figure traces straight back to the sample: North Electronics is one order, 3 × 149.99 = 449.97. South Clothing is 5 × 39.99 = 199.95. South Electronics is 4 × 149.99 = 599.96. West Electronics is 2 × 89.50 = 179.00. Across the four rows that is 14 units and 1,428.88 in revenue, and the cancelled 299.00 order is nowhere in it.

If you want that grand total as a row rather than as mental arithmetic, add a second Group & Aggregate step and leave Group by empty. With no grouping columns the operation collapses everything to a single row: quantity_sum 14, revenue_sum 1,428.88.

What the pipeline actually runs

Every step is its own view. Nothing gets folded together, so there is no single query with the filter tucked inside the GROUP BY. Click a card to see its SQL. The first step reads the source table, named after the file tab; every later step reads pipeline_output, the cumulative result of the steps above it.

-- Step 2, Filter
SELECT * FROM "orders" WHERE "status" != 'cancelled'

-- Step 3, Math
SELECT *, "quantity" * "unit_price" AS "revenue" FROM "pipeline_output"

-- Step 4, Group & Aggregate
SELECT "region", "category",
       SUM("quantity") AS "quantity_sum",
       SUM("revenue") AS "revenue_sum"
FROM "pipeline_output"
GROUP BY "region", "category"

Two things worth noticing. Column references are double-quoted throughout, which is what keeps a header like Unit Price working without you renaming it first. And there is no ORDER BY anywhere: Group & Aggregate does not emit one, so the app does not add one. Only operations that sort by definition (Limit Rows with a sort column, Top / Bottom Rows, the window operations) put an ORDER BY in the generated query. To put the biggest region on top for a screenshot, click the revenue_sum header in the grid. That sorts the view, not the data, and it adds no step.

Step 5: Export as Excel

Click the Export button and choose .xlsx. The summary table downloads as an Excel file, ready to attach to an email or drop into a shared drive.

Reusing this pipeline next month

The three cards you just built (Filter, Math, Group & Aggregate) stay in the sidebar. When next month's data arrives, load the new CSV and the same steps apply automatically. Column names need to match, but the data can be any size. A file with 10 rows and a file with 500,000 rows run through the same three cards with no changes.

Need another breakdown? By sales rep is just one more column in the Group by list. By month takes a little more: add an Extract Date Part step from the Date group first, with column date and part month, then group by the date_month column it creates. The Group & Aggregate guide covers the rest of the aggregate functions.

Build your summary pipeline →