how to get two decimal after counting

Kai Yang 166 Reputation points
2021-08-16T19:17:25.543+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,656 questions
0 comments No comments
{count} vote

Accepted answer
  1. Erland Sommarskog 112.7K Reputation points MVP
    2021-08-16T21:00:39.157+00:00

    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.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Viorel 118K Reputation points
    2021-08-16T19:48:02.743+00:00

    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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.