question

KaiYang-6525 avatar image
1 Vote"
KaiYang-6525 asked KaiYang-6525 commented

how to get two decimal after counting

Hello List,
I submitted the code below, I want to count the percentage of male in a table. the data is not clean, the values of 1 and Male in column gender are Male. I use sum function to count total number in the column and use count() function to get the total number form the table. Both of function are work well. But when I counting the percentage by "SUM(CASE WHEN gender in ('Male', '1') THEN 1 ELSE 0 END) / count() as pct_male", the result is 0. based on my maunale count, the value should be ~ 0.5. can you help me?
Thanks,


select
SUM(CASE WHEN gender in ('Male', '1') THEN 1 ELSE 0 END) as N_male,
count() as overall,
SUM(CASE WHEN gender in ('Male', '1') THEN 1 ELSE 0 END) / count(
) as pct_male
from gemd.Respondents_tidy

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered Viorel-1 commented

I replace 1.0 to 100.0, the result has two decimal place. But the value is 00 of decimal place. it seems the result still rounded.

Yes, I hinted there some magic going on. Well, not really magic, but issues related to the implicit type of numeric literals and some complicated rules for the type you get when you divide decimal values. I don't know them by heart myself.

The way I usally deal with this is to cast everything to float, and then cast the result of the division to the desired amount of decimals:

select 
         count(case when gender in ('Male', '1') then 1 end) N_male,
         count(*) overall,
         cast(
           cast(count(case when gender in ('Male', '1') then 1 end) as float)  / 
    cast(count(*) as float) as decimal(10, 2)) pct_male
     from gemd.Respondents_tidy

Or in a shorter way, but less explicit:

select 
         count(case when gender in ('Male', '1') then 1 end) N_male,
         count(*) overall,
         cast(1E2 * count(case when gender in ('Male', '1') then 1 end) / count(*) as decimal(10, 2)) pct_male
     from gemd.Respondents_tidy

The datatype of the literal 1E2 (which is equal to 100) is float, and then everything else is converted to float.

· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The first way, pct_male = 29.00,
the second way, pct_male =28.85. This is what I expect result. thank you

0 Votes 0 ·

I think that the second way is equivalent to the original suggestion, but I am not sure if you are interested.

0 Votes 0 ·

I think that the second way is equivalent to the original suggestion, but I am not sure if you are interested.

No, there is an important difference. The data type of 1E2 is float. The data type of 1.0 is, hold on,

DECLARE @s sql_variant = 1.0
SELECT sql_variant_property(@s, 'Basetype') AS type,
       sql_variant_property(@s, 'Precision') AS prec,
       sql_variant_property(@s, 'Scale') AS scale

numeric(2,1).

Whence the difference in results.

0 Votes 0 ·
Show more comments
Viorel-1 avatar image
0 Votes"
Viorel-1 answered KaiYang-6525 commented

Try something like this:

 select 
     count(case when gender in ('Male', '1') then 1 end) N_male,
     count(*) overall,
     cast(count(case when gender in ('Male', '1') then 1 end) * 1.0 / count(*) as decimal(10, 2)) pct_male
 from gemd.Respondents_tidy

Replace 1.0 with 100.0 to show percent.


· 8
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

To add to Viorel's posting: the reason you got the result zero is because when you divide to integers, you get integer division. When Viorel adds 1.0, he introduces a decimal value and this serves to implicitly cast the type of the integer values to decimal.

0 Votes 0 ·

I replace 1.0 to 100.0, the result has two decimal place. But the value is 00 of decimal place. it seems the result still rounded.

0 Votes 0 ·

For which N_male and overall?

0 Votes 0 ·

N_male = 13069, overall=45391, so the percentage should look like 28.79

0 Votes 0 ·

and now it is 29

0 Votes 0 ·
Show more comments