I have a payments table with the following fields:
- id (int)
- status (successful, failed, refunded)
- amount (int)
- pdate (Y-m-d format)
I need to write a query to get, for each day how many payments were successful, how many failed and how many were refunded, with the percentage for that day. My current query is this:
select pdate, status, count(*) as total from payments group by pdate, status
The query successfully returns how many payments were processed on each day and the status:
2019-04-01, successful, 10 2019-04-01, failed, 5 2019-04-01, refunded, 5 2019-04-02, successful, 12 2019-04-02, failed, 5 2019-04-02, refunded, 3
But I don't know how to make it calculate the percentage for each payment for each day. For example, for
2019-04-01 there were
20 payments so, the percentage of
successful payments =
How can I make it return this?
2019-04-01, successful, 10, 50% 2019-04-01, failed, 5, 25% 2019-04-01, refunded, 5, 25% 2019-04-02, successful, 14, 58.3% 2019-04-02, failed, 4, 16.6% 2019-04-02, refunded, 6, 25%
My problem is how to make it count the total number of payments on each day so I can calculate the percentage?
Thanks in advance!