Percentage based on another column, with group by-CodePudding

This is what I have:

create table Test(day int, status varchar(50), transactions int);
insert into Test(day, status, transactions) values(5, "success", 105);
insert into Test(day, status, transactions) values(5, "success", 105);
insert into Test(day, status, transactions) values(5, "failure", 40);
insert into Test(day, status, transactions) values(6, "success", 40);
insert into Test(day, status, transactions) values(6, "failure", 32);
insert into Test(day, status, transactions) values(7, "success", 552);
insert into Test(day, status, transactions) values(7, "failure", 4);

select day, status, sum(transactions) from Test
group by day, status

getting this one:

day status  sum(transactions)
5   success 210
5   failure 40
6   success 40
6   failure 32
7   success 552
7   failure 4

I want to have additional column, telling me per day how much is my success rate. E.g. 210/(210 40) for day 5, 40/(32 40) for day 6 etc. How to do it?

CodePudding user response：

This way you can get the rates (success/failure) for equivalent status type.

select t.day, t.status, sum(transactions), sum(transactions)/total  as rate
from Test t inner join (select day, sum(transactions) as total from Test group by day) total
on t.day = total.day
group by t.day, t.status

Output:

day status  sum(transactions)   rate
5   failure    40               0.2759
5   success    105              0.7241