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

Question

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

Answer 1

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;

Answer 2

You can do that using this query:

select gen_date, count(transactions.id) from

(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
 left JOIN  transactions
  on date(transactions.created) = date(gen_date)

where gen_date between '2021-12-01' and '2021-12-20'
group by gen_date;

if you want laravel query builder version:

    $select = DB::query()->selectRaw("gen_date, count(transactions.id)")->fromSub(function ($query) {
        $query->select("(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4)");
    }, "v")
        ->leftJoin('transactions', function ($join) {
            $join->on(DB::raw("date(transactions.created)"), "=", DB::raw("date(v.gen_date)"));
        })
        ->whereRaw("gen_date between '2021-12-01' and '2021-12-20'")
        ->groupBy('gen_date')
        ->get();

ps: the date range subquery I taken from here