Weird behavior of grouping in MySQL-CodePudding

I have a such table in MySQL Database:

CREATE TABLE metrics (
  id int(11) NOT NULL AUTO_INCREMENT,
  date date NOT NULL,
  value int(11) NOT NULL,
  PRIMARY KEY (id)
) ENGINE InnoDB AUTO_INCREMENT=1 CHARSET=utf8mb4;

With the following values:

INSERT INTO metrics (date, value)
VALUES 
    ('2022-04-01', 1),
    ('2022-04-02', 1),
    ('2022-04-03', 1),
    ('2022-04-04', 1),
    ('2022-04-05', 1),
    ('2022-04-06', 1),
    ('2022-04-07', 1),
    ('2022-04-08', 1),
    ('2022-04-09', 1),
    ('2022-04-10', 1),
    ('2022-04-11', 1),
    ('2022-04-12', 1),
    ('2022-04-13', 1),
    ('2022-04-14', 1);

I want to get sum of values grouping by weeks. Default week in MySQL is from Monday to Sunday, but I need to get grouping by week from Sunday to Saturday, so this is my query:

SELECT
    (DATE_SUB(date, INTERVAL WEEKDAY(date) DAY) - INTERVAL 86400000000 MICROSECOND) AS week_start,
    SUM(value) AS value__sum
FROM
    metrics
GROUP BY
    (DATE_SUB(date, INTERVAL WEEKDAY(date) DAY) - INTERVAL 86400000000 MICROSECOND)
ORDER BY
    week_start ASC;

INTERVAL 86400000000 MICROSECOND this interval is so strange because Django ORM do this. Anyway, I got such result:

week_start         |value__sum|
------------------- ---------- 
2022-03-27 00:00:00|         3|
2022-04-03 00:00:00|         7|
2022-04-10 00:00:00|         4|

As you can see week_start value is right, it's Sunday but value__sum contains data in wrong period Monday-Sunday. Perhaps I've missed something?

CodePudding user response：

Does this work for you?

SELECT
    DATE_SUB(
      date, INTERVAL WEEKDAY(DATE_SUB(date, INTERVAL -1 DAY)) DAY
    ) AS week_start,    
    SUM(value) as value__sum
FROM
    metrics
GROUP BY
    week_start
ORDER BY
    week_start ASC;

I subtracted the day inside of WEEKDAY.

This results in:

week_start	value__sum
2022-03-27	2
2022-04-03	7
2022-04-10	5

CodePudding user response：

You can simply use week(). If this is not splitting on the right day you can use week(date_add, interval 1 day)) replacing 1 with the number (positive or negative) that you need.

select
  week(date) "week",
  sum(value) "value"
from metrics
group by week(date)
order by week(date);

week | value
---: | ----:
  13 |     2
  14 |     7
  15 |     5

db<>fiddle here