-1

I have a SQL table:

CREATE TABLE test (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `date` date NULL,
 `distance` INT(10) DEFAULT NULL,
 PRIMARY KEY (`id`)
);

INSERT INTO test (date, distance) VALUES ('2020-01-01','1');
INSERT INTO test (date, distance) VALUES ('2020-01-15','3');
INSERT INTO test (date, distance) VALUES ('2020-02-10','1');
INSERT INTO test (date, distance) VALUES ('2020-02-20','4');
INSERT INTO test (date, distance) VALUES ('2020-05-06','8');
INSERT INTO test (date, distance) VALUES ('2020-11-12','2');
INSERT INTO test (date, distance) VALUES ('2020-11-13','5');

And what I want to do is to have a sum for every month from this year, but what is important to keep months with sum of zero even if I do not have data for this month

Here is the fiddle: https://www.db-fiddle.com/f/9KmGk6uxBQxSRxr5Qsid3S/2

I have tried something like:

SELECT YEAR_MONTH(`date`) as date, SUM(distance) FROM test GROUP BY `date`;
GMB
  • 216,147
  • 25
  • 84
  • 135
Tikky
  • 1,253
  • 2
  • 17
  • 36
  • _Small Hint_ Change the group by to `GROUP BY MONTH(`date`)` and you will get a better answer from the query you have – RiggsFolly Dec 17 '20 at 14:41
  • https://dba.stackexchange.com/questions/185079/get-previous-12-month-data-and-if-not-exist-then-add-zero-in-mysql – RiggsFolly Dec 17 '20 at 14:44

1 Answers1

0

Basically you need a calendar table that holds of the month of the year, than you can then left join with the table.

You can either create the table manually, or generate it on the fly with a recursive query (which requires MySQL 8.0):

with cte as (
    select date_format(current_date, '%Y-01-01') as dt
    union all select dt + interval 1 month from cte where dt < date_format(current_date, '%Y-01-01')  + interval 11 month
)
select c.dt, coalesce(sum(t.distance), 0) as sum_distance
from cte c
left join test t 
    on  t.date >= c.dt
    and t.date <  c.dt + interval 1 month
group by c.dt
order by c.dt
GMB
  • 216,147
  • 25
  • 84
  • 135