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 = 50%, failed = 25% and refunded = 25%.

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!

1 Answer 1

SELECT pdate, 
       COUNT(status) total, 
       SUM(status='successful') successful,
       SUM(status='failed') failed,
       SUM(status='refunded') refunded, 
       SUM(status='successful')/COUNT(status)*100 successful_percent,
       SUM(status='failed')/COUNT(status)*100 failed_percent,
       SUM(status='refunded')/COUNT(status)*100 refunded_percent
FROM payments
GROUP BY pdate
  • Thanks very much for your reply @Akina. I've edited my question and included an example of the "ideal" result set. Is it possible to have that structure via query? Commented Apr 10, 2019 at 12:51
  • Is it possible to have that structure via query? Of course. But now you MUST specify your MySQL version.
    – Akina
    Commented Apr 10, 2019 at 13:46
  • mysql Ver 14.14 Distrib 5.7.25, for Linux (x86_64) Commented Apr 10, 2019 at 13:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.