CODEWITHSUNDEEP
mysqlsql
NaD
NaD

Reputation: 141

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%

Upvotes: 0

Views: 111

Answers (4)

SUNIL KUMAR
SUNIL KUMAR

Reputation: 135

select SUB_GROUP, sum( if(STATUS='success', 1, 0) ) as total, (sum(if(STATUS='success', 1, 0) )100/count()) +'%' as Success_Rate from tab group by SUB_GROUP

Upvotes: 0

SUNIL KUMAR
SUNIL KUMAR

Reputation: 135

select SUB_GROUP, sum(case STATUS when 'success' then 1 else 0 end) as total, (sum(case STATUS when 'success' then 1 else 0 end)* 100/count(*)) +'%' as Success_Rate from table1

group by SUB_GROUP

Upvotes: 0

Rahul
Rahul

Reputation: 77866

Use a group by query like

select SUB_GROUP,
sum(case when STATUS = 'success' then 1 else 0 end) as total,
count(STATUS) / sum(case when STATUS = 'success' then 1 else 0 end)* 100 as Success_Rate
from table1
group by SUB_GROUP

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

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.

Upvotes: 1

Related Questions