Calculate percentage of a value in a column when count is given

Home / sql / Calculate percentage of a value in a column when count is given

Question:
I have a table with values below
id name approved count
3 az 1 10
3 az 0 5
2 az 1 10
2 az 0 5
3 bz 1 10
3 bz 0 5
2 bz 1 10
2 bz 0 5
I need to calculate the % of approved =1 with my final view that shows
id name Rate
3 az 66.66
3 bz 66.66
2 az 66.66
2 bz 66.66
Where rate is (10/15)*100 i.e (count of approved = 1/sum of count for that id,name combination)*100


Answer:
Here is one way using conditional aggregation
SELECT id,
NAME,
Isnull(Sum(CASE WHEN approved = 1 THEN [count] END) / Sum([count] * 1.0) * 100,0)
FROM your_table
GROUP BY id, NAME
* 1.0 is used to avoid integer division
Read more

Leave a Reply

Your email address will not be published. Required fields are marked *