Convert answer to percentage with two decimals place SQL

Question

I’m trying to figure out how to convert the Male Percentage column to a percentage with decimals to the hundredths

select Top 20 pa.State,
Sum(case when p.gender='M' then 1 else 0  end) as [Male Count],
Sum(case when p.gender='F' then 1 else 0 end) as [ Female Count],
100*sum(case when gender='m' then 1 else 0 end )/count(*) as [Male Percentage]

From [dbo].[Patients] as p
Join PatientAddresses as pa
on p.mrn=pa.MRN
group by pa.State

The results I got.

State	Male Count	Female Count	Male Percentage
UT	105	120	46
NC	1152	1123	50
WI	700	669	51
MA	1486	1424	51

Answer 1

SQL Server by default will report integer division as integer numbers. If you want to force two decimal places, use ROUND(x, 2), where x is a float. One way to make x a float here is to multiply the percentage by the float 100.0 instead of the integer 100.

SELECT TOP 20
    pa.State,
    COUNT(CASE WHEN p.gender = 'M' THEN 1 END) AS [Male Count],
    COUNT(CASE WHEN p.gender = 'F' THEN 1 END) AS [Female Count],
    ROUND(100.0*COUNT(CASE WHEN gender = 'm' THEN 1 END) / COUNT(*), 2) AS [Male Percentage]
FROM [dbo].[Patients] AS p
INNER JOIN PatientAddresses AS pa
    ON p.mrn = pa.MRN
GROUP BY
    pa.State;

Side note: Using TOP without ORDER BY does not make much sense, because it is not clear which 20 records you want to see. So, adding an ORDER BY clause here is probably what you want, unless you are OK with getting back 20 random states.

Edit:

If you want to view the output in SSMS with only two decimal places, and not just with a precision of 2 decimal places, then use CONVERT:

CONVERT(DECIMAL(10,2), 100.0*COUNT(CASE WHEN gender = 'm' THEN 1 END) / COUNT(*))