SQL: Why Rounded Percentages Don't Add Up to 100

The symptom

You report each channel's share of transactions as a whole percentage: ATM 4, branch 5, card 45, mobile 16, online 29. Someone adds them up and gets 99. Every share is rounded correctly, and the column still doesn't total 100.

Why it happens

round() handles each row alone. The exact shares are 3.82, 5.41, 45.20, 16.40, and 29.16 percent. Rounding moves ATM up by 0.18 and the other four down by 0.41, 0.20, 0.40, and 0.16. Those changes don't cancel: the four drops outweigh the one gain by exactly a point, and the total lands on 99. With other data the changes can just as easily add up to 101.

Showing more decimal places shrinks the gap without closing it. Here, one decimal place happens to total 100.0, but the same shares by transaction type total 99.9.

The fix

When the column has to total 100, as in a pie chart or a published table, use the largest remainder method. Round every share down, count how many points are missing, and give one each to the shares that lost the most. Here the floors total 98, so ATM (3.82) and branch (5.41) get the two missing points:

with shares as (
  select channel,
         count(*) as transactions,
         round(100.0 * count(*) / sum(count(*)) over (), 0) as pct
  from transactions
  group by channel
)
select channel, transactions, pct, sum(pct) over () as total_pct
from shares
order by channel

with shares as (
  select channel,
         count(*) as transactions,
         100.0 * count(*) / sum(count(*)) over () as exact_pct
  from transactions
  group by channel
),
floored as (
  select channel,
         transactions,
         floor(exact_pct) as pct,
         row_number() over (
           order by exact_pct - floor(exact_pct) desc, channel
         ) as remainder_rank
  from shares
),
allocated as (
  select channel,
         transactions,
         pct + case
                 when remainder_rank <= 100 - sum(pct) over () then 1
                 else 0
               end as pct
  from floored
)
select channel, transactions, pct, sum(pct) over () as total_pct
from allocated
order by channel

row_number() ranks the shares by what floor() cut off, largest first, with the channel name as a tie-breaker, so the same data always gives the same answer. Branch beats mobile for the last point by less than a hundredth (a remainder of 0.412 against 0.405), and that is the method doing its job. No share ends up a whole point or more from its exact value.

Or say that it is rounded

The adjusted numbers are a presentation choice: branch shows 6 percent though its exact share is 5.41. For an analysis rather than a published table, it is often more honest to keep plain rounding and add a note such as "Figures may not sum to 100 because of rounding". Whichever you choose, compute totals from the exact values, never by adding up the rounded ones:

select channel,
       count(*) as transactions,
       100.0 * count(*) / sum(count(*)) over () as exact_pct,
       round(100.0 * count(*) / sum(count(*)) over (), 0) as shown_pct
from transactions
group by channel
order by channel

Learn more

• Lesson 19.5 · Share of total
• Lesson 11.2 · Percentages and rates
• Why integer division makes percentages 0
• Reference · Window functions