# Getting total number of payments and percentage

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?

``````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. 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