Recipe

Calculate a percentage

Short answer: (part / whole) 100. The two real complications: (1) integer division silently truncates to zero, so cast to DECIMAL or FLOAT first; (2) divide-by-zero crashes, so guard with NULLIF to make the result NULL instead. The textbook safe form: ROUND(part 100.0 / NULLIF(whole, 0), 2) returns a clean rounded percentage with sensible behavior on edge cases.

Canonical SQL #

sql

-- Naive (broken on integers — truncates to 0).
SELECT (part / whole) * 100 AS pct FROM data;

-- Cast one side to force decimal division.
SELECT (part * 100.0 / whole) AS pct FROM data;
SELECT (part::DECIMAL / whole * 100) AS pct FROM data;          -- Postgres
SELECT (CAST(part AS DECIMAL) / whole * 100) AS pct FROM data;  -- portable

-- Safe form: handle divide-by-zero with NULLIF.
SELECT (part * 100.0 / NULLIF(whole, 0)) AS pct FROM data;

-- Rounded for display.
SELECT ROUND(part * 100.0 / NULLIF(whole, 0), 2) AS pct FROM data;

Example #

Loading SQL editor...

Common patterns #

sql

-- Conversion rate: paid / total signups.
SELECT
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE plan = 'paid')::DECIMAL
    / NULLIF(COUNT(*), 0),
    2
  ) AS conversion_rate_pct
FROM users;

-- Percentage of total (each row vs the whole).
SELECT
  region,
  revenue,
  ROUND(100.0 * revenue / SUM(revenue) OVER (), 2) AS pct_of_total
FROM regional_sales;

-- Percentage of group total (each row vs its group).
SELECT
  region,
  product,
  revenue,
  ROUND(100.0 * revenue / SUM(revenue) OVER (PARTITION BY region), 2) AS pct_of_region
FROM regional_sales;

-- Percentage change between two values.
SELECT
  prior_value,
  current_value,
  ROUND(100.0 * (current_value - prior_value) / NULLIF(prior_value, 0), 2) AS pct_change
FROM kpi_snapshots;

Common mistakes #

Integer division truncating to 0: 5 / 100 is 0 on every engine that uses integer division for integer operands. Multiply by 100.0 (decimal literal) instead of 100 to force decimal arithmetic.
No divide-by-zero guard: SELECT a / b where b can be 0 errors out and breaks the whole query. NULLIF(b, 0) returns NULL for zero denominators; the resulting NULL is usually the "no data to report" answer you want.
Calculating percentages of percentages: a 10% increase followed by a 10% decrease is not a return to the start (it's 99% of the original). When chaining percentages, work in absolute values and only convert at the end.
Mixing percentage formats: a column called tax_rate storing 0.20 (fraction) vs 20.00 (percentage) is the source of "the totals are 100x off" bugs. Pick one convention per column and document it (tax_rate_pct vs tax_rate_fraction).

Dialect notes #

Postgres: integer division is /; / with at least one decimal operand does decimal division. Cast with ::DECIMAL or use a decimal literal (100.0).
DuckDB: / always performs float division, even between integers (5 / 100 is 0.05); // is the integer-division operator. The cast/100.0 habit still pays off, keeping the same query correct on Postgres and friends.
MySQL: / always returns a decimal even for integer inputs (unlike most other engines). DIV is the integer-division operator. So 5 / 100 returns 0.05 on MySQL but 0 on Postgres.
SQL Server: integer division for integer operands. Same fix: cast or use decimal literal.
Oracle: integer division for integer operands. Same fix.
Snowflake / BigQuery / Redshift: / between integers returns decimal (warehouses tend to be lenient about this). Confirm before relying on the behavior.
SQLite: integer division for integer operands.

Tip

The defensive percentage template. Memorize ROUND(part * 100.0 / NULLIF(whole, 0), N). It handles integer division (the 100.0 decimal literal), divide-by-zero (the NULLIF), and display rounding (the outer ROUND). Use it as the default; only deviate when one of the three guards genuinely doesn't apply, and document why.