SQL rounding integers without decimal places
I have what seems like a simple problem. I just want to show the percentages without decimal places and add up to 100 cumulatively. 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 are added 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 ...
source to share
I just saw the answer above. Good job Gordon!
Here's a similar version of the answer:
SELECT CASE isMale
WHEN 'True'
THEN 'Male'
ELSE 'Female'
END AS Gender
,CAST(left(round(count(isMale) * 100.00 /(select count(*) from gendertable), 0),2) AS VARCHAR) + '%' AS GenderPercent
FROM genderTable
GROUP BY isMale
source to share
For your specific problem, I am getting numbers up to 100 when I do this:
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. Thus, the expression ROUND((count(isMale) * 100 / @total), 0)
performs an integer division computation - accepting a floor()
relationship before implementing it round()
.
There are ways to do what you want. They are easier to implement 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 the other lines and subtract that from 100 for one of the lines.
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 small integers are represented exactly, even with floating point representation, so there won't be a problem with round()
creating a value like 29.99999999999997 instead of 30.
source to share