How do you prevent integer division in a SQL percentage?

Convert an operand before dividing. In the SQLite example below, use 100.0 * converted_sessions / NULLIF(sessions, 0). Do not first divide two integers inside parentheses and then multiply by 100.0: the fractional part has already been discarded. Casting the completed quotient cannot recover it.

Database behavior varies by numeric type and dialect. The PostgreSQL mathematical operator reference explicitly documents truncation toward zero for integral division. Check your engine and operand types rather than assuming every SQL system handles the same expression identically.

Define the metric before fixing the formula

For this fictional agency report, conversion means a session containing at least one confirmed lead. Each session contributes at most one to the numerator. Both counts use the same eligibility rules and complete reporting period. An order count divided by sessions would be a different metric if a session can contain several orders.

US, UK, Germany, and France are reporting segments, not evidence about real market performance. No currency conversion or revenue assumption is needed for these counts. The France row deliberately has an incomplete denominator; the Germany row has a confirmed zero eligible sessions.

CREATE TABLE campaign_counts (
 market TEXT, sessions INTEGER, converted_sessions INTEGER
);
INSERT INTO campaign_counts VALUES
 ('US',200,7),('UK',80,2),('DE',0,0),('FR',NULL,1);

The formula that still returns zero

SELECT market,
       100.0 * (converted_sessions / NULLIF(sessions,0)) AS wrong_pct
FROM campaign_counts ORDER BY market;

US has 7 converted sessions out of 200: the intended result is 3.5%. UK has 2 out of 80: 2.5%. With integer operands, the parenthesized quotients become zero, so both displayed percentages are 0.0. A dashboard could incorrectly suggest that neither market generated a lead.

SELECT CAST(7 / 200 AS REAL);  -- 0.0: conversion happens too late
SELECT CAST(7 AS REAL) / 200;  -- 0.035: operand converted first

These examples are tested in SQLite. In PostgreSQL, an explicit CAST(converted_sessions AS numeric) before division is an option when decimal arithmetic is desired. Avoid relying on a display formatter to repair an already truncated value.

Calculate the percentage and retain a status

SELECT market, sessions, converted_sessions,
       100.0 * converted_sessions / NULLIF(sessions,0) AS conversion_pct,
       CASE WHEN sessions IS NULL THEN 'missing denominator'
            WHEN sessions = 0 THEN 'no eligible sessions'
            ELSE 'measured' END AS rate_status
FROM campaign_counts ORDER BY market;

market | sessions | converted_sessions | conversion_pct | rate_status
DE     | 0        | 0                  | NULL           | no eligible sessions
FR     | NULL     | 1                  | NULL           | missing denominator
UK     | 80       | 2                  | 2.5            | measured
US     | 200      | 7                  | 3.5            | measured

The decimal multiplier acts before division in this expression. NULLIF changes a zero denominator to NULL. The separate status explains whether the denominator is missing or there were no eligible sessions.

Why not replace every missing rate with 0%?

A measured 0% means there were eligible sessions and none converted—for example, 0 out of 20. Zero out of zero is undefined, while a missing session count is incomplete data. Collapsing all three cases into 0% conceals the difference between observed performance, no opportunity, and a measurement problem.

Keep an explicit status in the reporting data even if the chart displays a dash for undefined values. Also validate nonnegative counts and, under this session-based definition, converted_sessions no greater than sessions. The formula itself does not enforce those rules.

Choose one percentage scale for the dashboard

A fraction of 0.035 and a percentage value of 3.5 represent the same rate. If a BI percentage formatter expects a fraction, pass 0.035. Passing 3.5 to a formatter that multiplies by 100 produces 350%. Label exported fields clearly, such as conversion_fraction or conversion_pct, and verify one known case in the final display.

Combine complete segments explicitly

The complete positive-denominator segments contain 9 converted sessions and 280 sessions, so their combined rate is about 3.2143%. This is a complete-segment rate, not a fully observed all-market rate: France is excluded because its denominator is missing.

SELECT SUM(converted_sessions) AS converted_sessions,
       SUM(sessions) AS sessions,
       100.0 * SUM(converted_sessions) / NULLIF(SUM(sessions),0) AS conversion_pct
FROM campaign_counts
WHERE sessions > 0;

Do not quietly sum the France numerator while SUM ignores its missing denominator. State the coverage rule and report excluded segments alongside the result. Nor should you average 3.5% and 2.5% equally: the two measured markets have different session counts.

Checks before sharing the report

• Inspect operand types and convert before division.
• Test a small nonzero rate, a true zero rate, a zero denominator, and a missing denominator.
• Confirm whether the destination expects a fraction or a percentage value.
• Retain numerator, denominator, and coverage status beside each rate.
• Reconcile combined counts before calculating a combined rate.

Validation: six SQLite assertions passed for the broken percentages, corrected results and statuses, late versus early casts, a measured zero, and the complete-segment aggregate. No hosted PostgreSQL execution or performance benchmark is claimed.

Related worked examples

Use the weighted conversion-rate example for combining regional reports. Review confirmed lead tracking before defining the numerator, and NULL versus zero for missing-data interpretation.