Doing Math Across Columns: Add, Multiply, Round

Your data has a price column and a tax rate, but no tax amount. Or you have weight in pounds but need kilograms. Or there's a discount percentage that needs to be applied to get the final price. The raw numbers are there - you just need to combine them.

In a spreadsheet you'd add a formula column. In SQL you'd write an expression in your SELECT. In ExploreMyData, you use the Math operation to do arithmetic across columns without writing anything by hand.

Calculating tax amounts

Start with the most common case. You have a price column and need to calculate sales tax at 8.25%.

Open the toolbar and select Math from the Transform group. You'll see a field where you build an expression using your column names and arithmetic operators.

Enter the expression:

ROUND(price * 0.0825, 2)

The ROUND(..., 2) keeps the result to two decimal places. Without it, you'd get values like 8.249999999 instead of 8.25. Always round money.

To put the result in a new column, use Add Column from the Columns group instead, name it "tax_amount", and enter the same expression. Either way works - Math updates an existing column, Add Column creates a new one.

Applying discount percentages

Your data has a price column and a discount column (stored as a percentage, e.g., 15 means 15%). You need the discounted price.

The expression:

price * (1 - discount / 100.0)

A $200 item with a 15% discount becomes 200 * (1 - 15/100.0) = 200 * 0.85 = 170.

Notice the 100.0 instead of 100. This forces floating-point division. If both values are integers, 15 / 100 would give you 0 in some contexts. Using 100.0 avoids that.

Unit conversions

Converting between units is just multiplication by a constant:

• Pounds to kilograms: weight_lbs * 0.453592
• Miles to kilometers: distance_mi * 1.60934
• Fahrenheit to Celsius: (temp_f - 32) * 5.0 / 9.0
• Cents to dollars: amount_cents / 100.0

Create a new column for the converted value so you keep the original. Name it descriptively: "weight_kg", "distance_km", "temp_c".

Combining multiple columns

Math expressions can reference as many columns as you need:

Total order value with tax and shipping:

ROUND(subtotal + (subtotal * tax_rate / 100.0) + shipping_cost, 2)

Profit margin percentage:

ROUND((revenue - cost) / revenue * 100, 1)

Average of three scores:

ROUND((score_1 + score_2 + score_3) / 3.0, 1)

Handling NULLs in math

There's a gotcha with arithmetic on data that has missing values. In SQL (and DuckDB), any math involving NULL returns NULL. If shipping_cost is NULL for some rows, then subtotal + shipping_cost is also NULL for those rows - even if subtotal is perfectly fine.

The fix: wrap nullable columns in COALESCE() to provide a default:

subtotal + COALESCE(shipping_cost, 0)

Now if shipping_cost is NULL, it's treated as 0 instead of poisoning the whole calculation.

Rounding options

ROUND(value, decimal_places) is the function you'll use most. A few variations:

• ROUND(value, 2) - two decimal places (use for money)
• ROUND(value, 0) - round to nearest integer
• FLOOR(value) - round down
• CEIL(value) - round up

For display purposes, two decimal places is usually right for money and one for percentages. For intermediate calculations that feed into other steps, skip rounding to avoid compounding rounding errors - round only at the final step.

Chaining math with other operations

Math operations are pipeline steps like everything else. A typical workflow:

1. Convert Type: make sure price and discount are numeric (not text)
2. Fill Missing: replace NULL discounts with 0
3. Add Column: calculate final_price = price * (1 - discount / 100.0)
4. Add Column: calculate tax = ROUND(final_price * 0.0825, 2)
5. Add Column: calculate total = final_price + tax

Each step builds on the previous one. You can reference columns created in earlier steps. The pipeline runs top to bottom, and the SQL is always visible so you can verify the math.

Start calculating →