Skip to content
Advertisement

MySQL : Calculate percentage composition based on a specific value

I have the following MySQL table named Table1

MySql table

The Result can either be Pass or Fail. I want to calculate the percentage pass for each id.

Result must be the following table based on formulae: For each id, count Pass / Total Counts for given id. For example id 2 have 2 pass and 2 fail therefore percentage pass = 2/4

Intended Result

My Sql Query below is giving wrong results

SELECT  id,COUNT(Result) * 100 / (SELECT COUNT(Result) FROM aa WHERE Result = 'Pass') as Percent
FROM aa
GROUP BY id

Please help

Advertisement

Answer

You can do conditional aggregation like that:

select id, avg(result = 'Pass') as pass_ratio
from mytable
group by id

In the context of avg(), condition result = 'Pass' is evaluated as 1 if true and 0 if false. You can just average these values to get the ratio of “pass”.

Advertisement