Calculations and rounding

Arithmetic row by row, and the four traps behind most wrong numbers in reports.

SQL does math on each row, like a spreadsheet formula copied down a column.

SELECT opportunity_id,
       amount + 5000          AS with_onboarding_fee,
       amount - 2500          AS after_discount,
       amount * 1.03          AS after_3pct_increase,
       amount / 12            AS per_month
FROM opportunities;

Trap 1: order of operations

* and / run before + and -, so 100 + 50 * 2 is 200, not 300. Use parentheses whenever the order matters:

SELECT (amount + 5000) * 1.03 AS fee_then_increase
FROM opportunities;

Trap 2: integer division

Dividing one whole number by another behaves differently by database:

SELECT 7 / 2;   -- MySQL: 3.5     SQL Server, PostgreSQL, SQLite: 3

A win rate of 45 out of 50 comes back as 0 in SQL Server. Make one side a decimal first, and it works everywhere:

SELECT 7 * 1.0 / 2;   -- 3.5 on every database

Trap 3: dividing by zero

On SQL Server and PostgreSQL, dividing by zero is an error that stops the whole query. (MySQL and SQLite quietly return NULL instead.) NULLIF(x, 0) turns a zero into NULL, and dividing by NULL just returns NULL:

SELECT 45 / 0;                -- error on SQL Server and PostgreSQL
SELECT 45 / NULLIF(0, 0);     -- NULL, and the query keeps running
 
-- The real-world shape: a win rate for a rep who hasn't closed anything yet
won_deals * 1.0 / NULLIF(closed_deals, 0)

You’ll build counts like won_deals in the next lesson.

Guard every division whose bottom number could ever be zero.

Trap 4: NULL in math

Any arithmetic with NULL returns NULL:

SELECT 100 + NULL;                  -- NULL
SELECT 100 + COALESCE(NULL, 0);     -- 100: treat missing as zero on purpose

Decide whether a missing value means “unknown” or “zero”, then write the query to match.

Rounding

SQL Server calls it CEILING instead of CEIL. SQLite, which the sandbox below runs on, ignores negative digits, so ROUND(1234.567, -2) gives 1235 there.

Round only in the final result. Rounding each row and then adding them up builds in error.

Snowflake notes

Snowflake returns 3.5 for 7 / 2: dividing two whole numbers gives a decimal, like MySQL. Dividing by zero is an error, like SQL Server. Snowflake has two shortcuts for the guard:

NULLIF returns NULL instead, which works on every database. Pick on purpose: 0 means “measured, and none”, NULL means “nothing to measure yet”.

Try it

For every open opportunity, show its name, its amount, and its amount after a 3% increase rounded to 2 decimal places.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson