sqlsql-serverselectpercentage

Calculate percentage of total returns 0

I wish to return a percentage grouped by each id_cerinta, like how many of the subcerinteProiect recordings are having acoperire= 'acoperita' divided to all the subcerinteProiect recordings according to the id_sarcina they are grouped by

enter image description here

I tried something like : 

SELECT 
    id_cerinta,
    SUM(CASE WHEN acoperire = 'acoperita' THEN 1 ELSE 0 END) 
        / COUNT(*) * 100 AS nr_subcerinte_acoperit 
FROM 
    subcerinteProiect 
GROUP BY 
    id_cerinta

But it doesn't work too good. It returns only 0..

Solution

  • This is due to integer math. Try these:

    SELECT 5/100, 57/100, 99/100;

    Results are all 0.

    Now try:

    SELECT 5.0/100, 57.0/100, 99.0/100;

    Better results, right?

    So for your query:

    SELECT 
    id_cerinta, 
    CONVERT(DECIMAL(5,2), 
        SUM(CASE WHEN acoperire = 'acoperita' THEN 1 ELSE 0 END) 
            / COUNT(*) * 100.0) AS nr_subcerinte_acoperit 
FROM 
    subcerinteProiect 
GROUP BY
    id_cerinta