Select and count data per day

Question

I have a following table with columns:

id | number | created_at
1  | A11    | 2020-01-01 06:08:19
2  | A21    | 2020-01-04 06:08:19

How do I query all the data in a date range from specific date and count all data per day?

I tried something like that :

SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS the_date , COUNT(*) AS count 
FROM `transactions`
WHERE created_at BETWEEN DATE_FORMAT('2020-01-01', '%Y-%m-%d') AND DATE_FORMAT('2020-01-04', '%Y-%m-%d') 
GROUP BY the_date

Then i got data like that :

the_date    | count 
2020-01-01  | 1    
2020-01-04  | 1

I want to achieve

the_date    | count 
2020-01-01  | 1
2020-01-02  | 0
2020-01-03  | 0
2020-01-04  | 1

Wei Lin · Answer 1 · 2020-01-18T06:57:52.327

if your version is below mysql 8.0 then you can use this script :

step1 : create a sequence N rows table :

create table sequence(id int);
create procedure insert_data_proc(in v_i int)
begin
declare i int default 0;
while i < v_i
do
insert into sequence values (i);
set i = i + 1;
end while;
end;
call insert_data_proc(1000);
drop procedure insert_data_proc;

step2 : query the table and left join your table's by mindate,maxdate,datediff

select 
    t1.created_at the_date
    ,case when count is null then 0 else count end as count 
from (
    select date_add(t2.mincreated_at , interval id day) created_at
    from sequence t1
    left join (
       select datediff(max(created_at),min(created_at)) diff
          ,date(min(created_at) ) mincreated_at
          ,date(max(created_at) ) maxcreated_at
       from transactions
    ) t2 on 1=1
    where t1.id < t2.diff+1
) t1
left join (
    select date(created_at) created_at,count(1) count
    from transactions
    group by date(created_at)
) t2 on t1.created_at = t2.created_at
order by the_date

note : if your data's days over 1000 day then you only need to increase the SP value.

[Online Demo Link MySQL 5.7 | db<>fiddle](https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=57d3e60bb2b918e8b6d2d8f3d5e63a6c )

Sachi.Dila · Answer 2 · 2020-01-18T06:54:32.350

I will give a suggestion for you to do this,

1 Solution

Create temporary table and add the dates and then join with the transactions table

create temporary table tempcalander
as
select * from 
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) dates 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 dates between '2020-01-01' and '2020-01-04';

SELECT DATE_FORMAT(dates, '%Y-%m-%d') AS the_date , COUNT(created_at) AS count 
FROM transactions t right join tempcalander c on t.created_at = c.dates
WHERE dates BETWEEN DATE_FORMAT('2020-01-01', '%Y-%m-%d') AND DATE_FORMAT('2020-01-04', '%Y-%m-%d') 
GROUP BY the_date

2 Solution

you can create a separate table to add your dates.

CREATE TABLE calendar
(
    dates date PRIMARY KEY
) ;

Then add you dates to this table,

INSERT INTO 
     calendar (dates)
VALUES
    ('2020-01-01'),
    ('2020-01-02'),
    ('2020-01-03'),
    ('2020-01-04'),
    ('2020-01-05'),
    ('2020-01-06') ;

after you can join the the transactions table with the calendar table and get the output

SELECT DATE_FORMAT(dates, '%Y-%m-%d') AS the_date , COUNT(created_at) AS count 
FROM transactions t right join calendar c on t.created_at = c.dates
WHERE dates BETWEEN DATE_FORMAT('2020-01-01', '%Y-%m-%d') AND DATE_FORMAT('2020-01-04', '%Y-%m-%d') 
GROUP BY the_date

score 0 · Accepted Answer · answered Jan 18 '20 at 06:32

you can use like this :


SET @date_min = '2019-01-01';
SET @date_max = '2019-01-04';

SELECT
   date_generator.date as the_date,
   IFNULL(COUNT(transactions.id), 0) as count
from (
   select DATE_ADD(@date_min, INTERVAL (@i:=@i+1)-1 DAY) as `date`
   from information_schema.columns,(SELECT @i:=0) gen_sub 
   where DATE_ADD(@date_min,INTERVAL @i DAY) BETWEEN @date_min AND @date_max
) date_generator
left join transactions on DATE(created_at) = date_generator.date
GROUP BY date;

so here I am creating a temporary table date_generator will dates in between of given date range and join to with your main table (transactions).

output as expected:

the_date    | count 
2020-01-01  | 1
2020-01-02  | 0
2020-01-03  | 0
2020-01-04  | 1

Select and count data per day

3 Answers3