[FIXED] How to (group by) date for every day in mysql

Issue

For my site admin panel, i need to show statistics for payments between two date

My payments table fields:

+--------------------+--------------+------+-----+-------------------+-----------------------------+
| Field              | Type         | Null | Key | Default           | Extra                       |
+--------------------+--------------+------+-----+-------------------+-----------------------------+
| id                 | int(11)      | NO   | PRI | NULL              | auto_increment              |
| price              | int(11)      | NO   |     | 0                 |                             |
| created            | timestamp    | NO   |     | CURRENT_TIMESTAMP |                             |
+--------------------+--------------+------+-----+-------------------+-----------------------------+

I need to get the payments data between tow days, day by day by php laravel to this structure

// date between 2021-12-01 and 2021-12-03

$data = [
    [
        'date' => '2021-12-01',
        'count' => 5 // number of payments records in 2021-12-01 in payments table
    ],
    [
        'date' => '2021-12-02',
        'count' => 0 // number of payments records in 2021-12-01 in payments table
    ],
    [
        'date' => '2021-12-03',
        'count' => 15 // number of payments records in 2021-12-01 in payments table
    ],
];

You should know maybe a day i haven't any payment and in database this day hasen't any records. but i need to show this day with count of 0

I don't know how do this

Solution

Using a recursive common table expression (in mysql 8 or mariadb 10.2+) to create your table of dates (here, reporting dates from 2021-01-01 to 2021-01-31):

with recursive dates as (
    select date('2021-01-01') date
    union all
    select dates.date + interval 1 day from dates where dates.date < '2021-01-31'
)
select dates.date, count(payments.id)
from dates
left join payments on date(payments.created)=dates.date
group by 1;

Answered By - ysth