[SOLVED] How to count values in MySQL for last 10 days and two different sets

Issue

I have a Laravel dashboard which populates some grapgh for last 10 days activity from that user based on two different status

Sharing a demo table rows for better understanding

id | sender | amount | status  | created_at
1  | Ali    | 2      | success | 2022-03-10
1  | Hasan  | 1      | success | 2022-03-10
1  | Ali    | 1      | failed  | 2022-03-10
1  | Hasan  | 5      | failed  | 2022-03-11
1  | Yousuf | 1      | success | 2022-03-11

I want to write a query to get all the records counted with each status for last 10 days

so the end result will be more like

2022-03-10
Success : 3
Failed : 1

2022-03-11
Success : 1
Failed : 5

I have formatted it for better understanding but I am okay If I get data for last 10 days
so there will be total 20 rows including (10 success counts + 10 failed counts)

For now I run 20 queries to count amount for last 10 days with 2 different status and this goes for each user so that creates load on server

Select count(*) as aggregate from history where sender = 'Ali' and status= 'sender' and day(created_at) = '10'

Select count(*) as aggregate from history where sender = 'Ali' and status= 'failed' and day(created_at) = '10'

.
.
.

Select count(*) as aggregate from history where sender = 'Ali' and status= 'sender' and day(created_at) = '1'

Select count(*) as aggregate from history where sender = 'Ali' and status= 'failed' and day(created_at) = '1'

Solution

To get all the records counted with each status for last 10 days, you need the where condition for the last 10 days based on the current_date, try:

select created_at,
       sum(case when status='success' then amount end) as success,
       sum(case when status='failed' then amount end) as failed
from history
WHERE created_at >= DATE_ADD(CURDATE(), INTERVAL -10 DAY)
group by created_at;

Result:

created_at    success failed
2022-03-10        3    1
2022-03-11        1    5

Demo

Answered By – Ergest Basha

Answer Checked By – Katrina (BugsFixing Volunteer)