NaD NaD - 10 days ago 6
MySQL Question

Getting the Percent in the same table

i'm having difficult time getting the percent on the same table.

i want to get the success rate per sub_group. success / total transaction * 100

STATUS, GROUP, SUB_GROUP
success, 2004, 80007022
success, 2004, 80007022
success, 2004, 80007022
success, 2004, 80007002
error, 2004, 80007002
error, 2004, 80007002
error, 2004, 80007002
error, 2004, 80007002
error, 2004, 80007002
error, 2004, 80007002
success, 2003, 80007007
success, 2003, 80007007
success, 2003, 80007007
success, 2003, 80007007
success, 2003, 80007007
success, 2003, 80007007
success, 2003, 80007007
success, 2003, 80007007
success, 2003, 80007007
success, 2003, 80007007
error, 2003, 80007007
error, 2003, 80007007
error, 2003, 80007007


Expected Results:

SUB_GROUP, Total, Success_Rate
80007022, 10, 40%
80007007, 13, 76.92%

Answer

Use conditional aggregation:

select sub_group, count(*),
       avg(status = 'Success') * 100
from t
group by sub_group;

This uses the MySQL shorthand that treats boolean expressions as integers in a numeric context, with "1" for true and "0" for false. Hence, the avg() is calculating the percent of successes among the rows in each group.