'Percentage based on another column, with group by
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:
day status sum(transactions) rate
5 success 210 0.8400
5 failure 40 0.1600
6 success 40 0.5556
6 failure 32 0.4444
7 success 552 0.9928
7 failure 4 0.0072
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
