sqlconcatenationrounding

How to round to 100 in SQL

select 
    concat(round((sum(gender='M')/count(gender)) * 100, 100), '%') as per_num
from 
    patients

The question is to find the % of patients who's gender is M and round to the nearest hundreth with percentage sign.

The syntax shown here is displaying the answer as 0%.

Solution

  • You need to cast the numerator or denominator to a float type, which can be done by either explicitly casting it or multiplying by 1.0 as shown below. To get the final value rounded, cast it as numeric type before the concatenation operation.

    SELECT concat(
  CONVERT(NUMERIC(10,2), (sum(CASE WHEN gender='M' THEN 1 ELSE 0 END)*1.0/count(gender))*100),'%') as per_num
FROM patients;