SQL Rounding integers with no decimals

Question

I have what seems like a simple problem. I just want to show percentages with no decimal places and have the total add to 100. Here's a little snipit:

create table genderTable
(person varchar(10),
isMale varchar(5))

insert into genderTable values ('Mary', 'false')
insert into genderTable values ('Frank', 'true')
insert into genderTable values ('Bill', 'true')
insert into genderTable values ('Jessie', 'false')
insert into genderTable values ('Sue', 'false')
insert into genderTable values ('Beth', 'false')
insert into genderTable values ('Kris', 'false')

declare @total as int
set @total = 7
select
CASE isMale
   WHEN 'True' THEN 'Male'
   ELSE 'Female'
END as Gender,
CASE 
WHEN @total > 0 THEN ROUND((count(isMale) * 100 / @total), 0)
ELSE 0
END as GenderPercent
from genderTable
group by  isMale

The totals add to 99% instead of 100%. I've tried various rounding, but I either get one decimal or 99%. Any help? Please keep in mind, in another example, I have to do the same for ethnicity where there are more than two values, so subtracting from 100 probably won't work...

Answer 1

For you particular problem, I get numbers that add up to 100 when I do:

select isMale, count(*), sum(count(*)) over (),
       round(100.0 * count(*) / sum(count(*)) over (), 0)
from genderTable t
group by isMale;

The actual problem with your implementation is that SQL Server does integer arithmetic. So, the expression ROUND((count(isMale) * 100 / @total), 0) is doing the calculation with integer division -- taking the floor() of the ratio before implementing the round().

There are ways to do what you want. They are more easily implemented in SQL Server 2012+ than in earlier versions:

select isMale,
       round(100.0 * cnt / tot, 0) as p,
       (case when seqnum = 1
             then 100 - sum(round(100.0 * cnt / tot, 0)) over (order by seqnum desc rows between unbounded preceding and 1 preceding)
             else round(100.0 * cnt / tot, 0) 
        end) as p_tot_100
from (select isMale, count(*)*1.0 as cnt, sum(1.0*count(*)) over () as tot,
             row_number() over (order by isMale) as seqnum
      from genderTable t
      group by isMale
     ) t;

The idea is to sum the rounded versions of all other rows and subtract this from 100 for one of the rows.

EDIT:

The "integer" versions of these:

select isMale, count(*), sum(count(*)) over (),
       cast(round(100.0 * count(*) / sum(count(*)) over (), 0) as int)
from genderTable t
group by isMale;

and:

select isMale,
       round(100.0 * cnt / tot, 0) as p,
       cast((case when seqnum = 1
                  then 100 - sum(round(100.0 * cnt / tot, 0)) over (order by seqnum desc rows between unbounded preceding and 1 preceding)
                  else round(100.0 * cnt / tot, 0) 
             end) as int) as p_tot_100
from (select isMale, count(*)*1.0 as cnt, sum(1.0*count(*)) over () as tot,
             row_number() over (order by isMale) as seqnum
      from genderTable t
      group by isMale
     ) t;

I'm pretty sure that small integers are represented exactly, even with floating point representation, so there will be no problem of round() producing a value such as 29.99999999999997 instead of 30.