Calculating Percentages and Ratios Across Columns

You have a sales report. Each row is an order with a product category and revenue amount. Your manager wants to know: what percentage of total revenue does each category represent?

Sounds simple until you try to do it. The percentage for any single row depends on the sum of all rows. You can't compute it with just the data in that row. In a spreadsheet, you'd put a SUM formula somewhere, then divide each cell by that fixed reference. It works, but it's fragile and ugly.

In SQL, this is what window functions are for. In ExploreMyData, you don't have to write the SQL yourself.

Simple ratio between two columns

Let's start with the straightforward case. Your data already has both numbers in the same row. Say you have completed_orders and total_orders per region. You want a completion rate.

Click the green + in the Pipeline panel and select Math from the Transform group. Math is a single free-text formula box, not a pair of operand pickers, so type the whole thing with the column names in double quotes:

ROUND("completed_orders" * 100.0 / "total_orders", 1)

Set Apply results into to New Column and call it completion_pct. Existing Column overwrites something you already have, and a blank name gives you a column called result.

Note the 100.0 instead of 100. If both columns are integers, integer division would truncate the result. Multiplying by a decimal forces floating-point math. A small detail that trips people up constantly.

Percentage of a grand total (this is the tricky one)

Back to the original problem. You have individual order rows with a revenue column. You want each row to show what percentage of total revenue it represents. The denominator isn't in any single row, it's the sum across all rows.

This is where window functions come in. A window function computes a value across a set of rows but returns a result for each individual row. What you want on every row is SUM("revenue") OVER (), the grand total.

Here's the part that catches people out: the Window Function dropdown has no SUM. It holds exactly six entries, and they are ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG and RUNNING TOTAL (cumulative sum). The one you want is RUNNING TOTAL, used in a way that doesn't look like a running total at all.

Click the green + in the Pipeline panel and select Window Function from the Aggregate group. Pick RUNNING TOTAL (cumulative sum), set Column to revenue, leave Partition by empty and, this is the bit that matters, leave Order by empty too. Name the output column total_revenue.

With no Order by column there is no frame clause to generate, so the step comes out as a plain whole-table sum:

SELECT *, SUM("revenue") OVER () AS "total_revenue" FROM "sales"

Every row gets the same number. Pick an Order by column and you get something else entirely: the frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW appears and the column turns into a real running total that climbs down the table. That's a useful column, but it's a terrible denominator. If your total_revenue changes from row to row, an Order by column is why.

Now add a Math step to divide:

ROUND("revenue" * 100.0 / "total_revenue", 2)

Apply that into a new column called revenue_pct, and every order shows its share of the total.

There's a shortcut if you already know your table's name: a Math formula can hold a scalar subquery, so "revenue" / (SELECT SUM("revenue") FROM sales) * 100 does the job in one step. Here sales is the name on the file tab, or pipeline_output once there are steps above it. The window route stays my default because you never have to type a table name that might change under you.

Percentage within a group

The question changes slightly. Instead of "what percentage of all revenue is this order?", you want "what percentage of its category's revenue is this order?"

Same RUNNING TOTAL step, one change: set Partition by to category, and still leave Order by empty. That tells DuckDB to compute a separate sum for each category.

SELECT *, SUM("revenue") OVER (PARTITION BY "category") AS "category_total" FROM "sales"

Now a row in "Electronics" gets the Electronics total, and a row in "Clothing" gets the Clothing total. Divide as before with a Math step:

ROUND("revenue" * 100.0 / "category_total", 2)

In the four rows above, the two Electronics orders share a category_total of 22300, so order 101 comes out at 56.05% of its category and order 103 at 43.95%. Clothing and Sports have one order each, so both sit at 100% of their own category.

This is genuinely hard to do in a spreadsheet. You'd need SUMIF or a pivot table. Here it's two steps.

Handling division by zero

If a group has zero total revenue, or your denominator column holds zeros, the division stops being meaningful. Guard it in the formula box:

CASE WHEN "total_revenue" = 0 THEN 0 ELSE ROUND("revenue" * 100.0 / "total_revenue", 2) END

Note there is no AS on the end. The output name comes from the "Apply results into" field, and typing an alias into the formula as well produces two aliases and a syntax error. This returns 0 instead of an empty cell. Not glamorous, but real data always has the edge case in it somewhere.

Verifying the percentages add up

A good sanity check: your percentage column should sum to 100, or close to it once rounding has had its say. Add Group & Aggregate from the Aggregate group, leave "Group by" empty so you get one global row, and add a single aggregation: SUM of revenue_pct. There's no output-name field, the result always comes back as revenue_pct_sum. If you partitioned by category, put category in "Group by" instead and every group should land on 100.

If it doesn't, check for NULL revenue values. NULLs are excluded from SUM but still produce rows, which means the percentages of the non-NULL rows won't add up to 100. Fill missing revenue values with 0 first if this matters for your analysis.

Start calculating percentages →