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

ysth · Accepted Answer · 2022-01-17T06:48:40.170

5

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;

edited Jan 17 '22 at 06:48

answered Jan 17 '22 at 06:09

ysth

96,171
6
121
214

1

how to implement in laravel query builder? – mohammad13 Jan 17 '22 at 07:10
@mohammad13 There's an extension https://github.com/staudenmeir/laravel-cte that provides support for recursive table expressions, but I don't know enough about laravel to show how to use it – ysth Jan 17 '22 at 08:07

Muhammad Dyas Yaskur · Answer 2 · 2022-01-17T06:25:38.360

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

score 0 · Answer 3 · answered Jan 17 '22 at 05:51

0

You can use DB::raw():

$data = DB::raw('select left(created_at, 10) as date, count(1) as count from payments group by date;');

answered Jan 17 '22 at 05:51

Joundill

6,828
12
36
50

you should know. maybe a day i haven't any payment and in databse this day has any records. but i need to show this day with count of 0 – mohammad13 Jan 17 '22 at 05:56
Do that in a loop in your controller/frontend – Joundill Jan 17 '22 at 05:57
1

i do but this is send many requests to database and this is actually incorrect way – mohammad13 Jan 17 '22 at 05:59
No, you do one call to the database with the code I wrote, and then populate the empty days with in your controller/frontend. – Joundill Jan 17 '22 at 06:00

score 0 · Answer 4 · answered Jan 17 '22 at 06:04

this code can show you the days of between two days

select * from 
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
 (select 0 i 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 i 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 i 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 i 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 i 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
where selected_date between '2021-12-01' and '2021-12-03'

i hobe this code can help you

This doesn't answer the question entirely. – S M Iftakhairul Jan 17 '22 at 06:23 — S M Iftakhairul, Jan 17 '22 at 06:23

score -1 · Answer 5 · answered Jan 17 '22 at 06:07

-1

SELECT DATE(created) AS `date`, COUNT(*) AS `count`
FROM source_table
-- WHERE created BETWEEN @period_start AND @period_finish
GROUP BY `date`;

answered Jan 17 '22 at 06:07

Akina

39,301
5
14
25

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

5 Answers5