Mysql groups by week across years

Mysql groups by week across years

SQL code use employees; create table sales (id int auto_increment, date datetime not null, cost int unsigned not null, primary key (id); insert into sales (date, cost) values ('2017-12-31 ', 2010); insert into sales (date, cost) values ('2017-01-01', 100); insert into sales (date, cost) values ('2017-01-02 ', 2011); insert into sales (date, cost) values ('2017-01-06', 100); insert into sales (date, cost) values ('2014-01-10 ', 2011); SELECT * FROM employees. sales; select week (date), sum (cost) from sales group by week (date); select floor (datediff (date, '2017-01-01 ')/7) as, sum (cost) from sales group by floor (datediff (date, '2017-01-01 ')/7);/* Final Solution */select date_add ('2017-01-01 ', interval floor (datediff (date, '1970-01-01 ')/7) * 7 day) as week_start, date_add ('1970-01-01', interval floor (datediff (date, '2017-01-01 ')/7) * 7 + 6 days) as week_end, sum (cost) from sales group by floor (datediff (date, '2017-01-01 ') /7 );