Integer Division in T-SQL: Why Your Percentages Come Out Zero

The dashboard says zero percent, yet three of four rows qualify. Integer division discarded the fraction before the report formatted the result. Change an operand to decimal before dividing, then round the result you intend to show.

Reproduce the Integer Division Surprise

Run the small expressions first. Multiplying the result of 3 / 4 by 100 preserves zero. Multiplying before dividing gives 75 for these values, but it can overflow on large counts and still discards fractional percentages. A decimal operand before division is the clean repair.

SELECT 3 / 4 AS integer_result,
       (3 / 4) * 100 AS too_late,
       100 * 3 / 4 AS whole_percent,
       100.0 * 3 / 4 AS decimal_percent,
       CAST(3 AS decimal(19,4)) / 4 * 100 AS cast_percent;

I show this expression to a report owner before touching a real query. It makes the issue visible without blaming the chart. Which operation in your calculation happens first? Parentheses and data types decide the answer, not the display format chosen by the reporting tool.

Understand the Type Rule

SQL Server chooses a result type from operand types and data type precedence. With two int operands, the quotient is an int and the fraction is truncated toward zero. The decimal literal 100.0 is not an integer, so decimal arithmetic participates from the multiplication onward. A CAST makes the intended precision explicit and is clearer when the calculation will be reused in a view or procedure.

SELECT SQL_VARIANT_PROPERTY(3 / 4, 'BaseType') AS integer_type,
       SQL_VARIANT_PROPERTY(3.0 / 4, 'BaseType') AS decimal_type;

Do not assume that a final CAST repairs an earlier integer division. CAST(3 / 4 AS decimal(10,2)) casts zero to 0.00. Cast an operand before the slash. For financial or compliance reporting, choose a decimal precision and scale that covers the largest numerator and preserves the desired fractional result.

Find Integer Division Hidden in Counts

COUNT returns an integer. A report that divides a qualifying count by a total count will therefore use integer division unless another operand changes the type. COUNT_BIG returns bigint, but bigint divided by bigint still loses the fraction. A window total does not change the rule either; it merely provides the denominator in each row.

WITH counts AS
(
    SELECT RegionID,
           COUNT(*) AS region_count
    FROM dbo.Orders
    GROUP BY RegionID
)
SELECT RegionID, region_count,
       region_count / SUM(region_count) OVER () AS wrong_share,
       100.0 * region_count /
           NULLIF(SUM(region_count) OVER (),0) AS percent_of_total
FROM counts;

Replace dbo.Orders with a real table. NULLIF prevents division by zero if the denominator expression can be zero. A grouped count over nonempty rows will not be zero here, but the guard is useful in broader formulas. The result is NULL when there is no meaningful denominator, which is more honest than inventing a zero percentage.

Watch Integer Division Between Two SUMs

SUM of an INT expression produces an INT result, so two SUM expressions can produce integer division. It can also overflow before the division if the total exceeds INT range. Casting the input to a wide decimal or bigint before SUM addresses the accumulation risk; using decimal before division retains the fraction. Do not add a CAST only around the finished SUM if overflow is possible inside it.

SELECT SUM(Amount) / SUM(Budget) AS wrong_ratio,
       SUM(CAST(Amount AS decimal(19,4))) /
           NULLIF(SUM(CAST(Budget AS decimal(19,4))),0) AS ratio
FROM dbo.DepartmentLedger;

These are sample names. Decide whether negative values, returns, and missing budgets belong in the numerator or denominator. Correct arithmetic does not make the business definition correct. I ask the report owner for two hand-checked rows and one zero-denominator case before promoting a rewritten expression.

Round Only the Displayed Result

Keep sufficient precision through division, multiplication, and aggregation. Round at the end to the number of decimal places the report needs. If each row is rounded first and then summed, the displayed percentages can drift away from 100.00. This is a presentation choice, not a reason to change the underlying exact counts.

WITH counts AS
(
    SELECT RegionID, COUNT_BIG(*) AS n
    FROM dbo.Orders GROUP BY RegionID
)
SELECT RegionID, n,
       ROUND(100.0 * n / NULLIF(SUM(n) OVER (),0),2)
           AS percentage
FROM counts;

ROUND returns a numeric result with the expression's type; a client can still format trailing zeroes differently. Keep the raw count beside the percentage while validating a report. A row count and a calculated 0.00 percent can be reasonable when the share is very small, but it should result from deliberate rounding, not integer truncation.

Test Extremes and Grouping

Test a numerator smaller than the denominator, equal counts, an empty input, a zero denominator, large totals, and negative inputs if those are valid. A percentage query grouped by region can also have a denominator scoped to the wrong partition. In a window expression, OVER () means all returned groups, while PARTITION BY creates separate totals. Verify which total the business question asks for.

I look first for an intermediate CTE when a report shows a row of zero-percent values; two COUNT results can already have divided there. The final SELECT cast the result to decimal, which made every zero look neatly formatted. We moved the decimal conversion into the CTE before division and compared the report with manually counted rows. The correction was one character in the expression and a full validation of the denominator.

Keep the Math Readable

For an average, examine both SUM and COUNT input types. A decimal numerator with an integer denominator retains fractional precision, while a cast placed outside the final quotient is too late. If an application assembles SQL text, inspect the submitted expression rather than the report designer formula; the generated query can introduce a new integer cast. Keep a test that compares raw counts with the displayed percentage after each report change.

Name intermediate counts, place the decimal conversion near the operand, and make the zero-denominator rule visible. Avoid a maze of nested CAST and ROUND calls around a formula no one can audit. If a percentage feeds a later calculation, preserve the unrounded decimal value and format only at the edge of the application.

An execution plan can show a Compute Scalar for the expression, but the first diagnostic is simpler: inspect the input and result data types. The engine is following the types it was given. A useful review question is, "Where does this expression first become decimal?" If the answer is after the slash, the fraction has already been lost.

Related reading on this blog: Banker's Rounding and How to Fix Error 8134 Divide by Zero Error Encountered.

Formatting is not a repair for integer division, it is the final step after decimal arithmetic.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.