How to get SQL WEEK() to start on 1st of Jan every year, produce a full 7 days and be able to groupby it

I'm trying to write a query to produce weekly sales totals over the years to produce a comparison on a graph. Trouble is, I've now discovered that week 0 or 1 (for example) doesn't necessarily start on the 1st of January each year. So when I have a graph labelled 1-7 Jan, 8-14th Jan etc, the data in those columns wont necessarily be from those dates.

Is there anyway I can force/specify WEEK() starts on the 1st of Jan. I've been looking into the modes and there doesn't seem to be an option that would do this. It's options result in either week 1 (or 0) starting on a Sunday, 4 or more days in the current year or on a Monday (or with a range of 0-53 or 1-53)... but not from the 1st of Jan and for a full 7 days.

//All observations below are for 2025
//Results in 1st to 4th Jan for week 0 (range 0-53)
//Week 1 starting from the 5th
WEEK (orders.created_at, 0) AS week
 
//Results in 1st to 5th Jan for week 1 (range 0-53)
//Supposed to be from 0 but it starts at 1
//with week 2 starting from the 6th
WEEK (orders.created_at, 1) AS week 

//Results in 5th to 11th Jan for week 1 (range 1-53)
//Placed 1st to 4th Jan (what should have been week 1) in week 52!
//Week 2 starting from the 12th
WEEK (orders.created_at, 2) AS week
 
//Results in 1st to 5th for week 1 (range 1-53)
//Week 2 starting from the 6th
WEEK (orders.created_at, 3) AS week 

//Results in 1st to 4th for week 1 (range 0-53)
//No week 0 to start
//with week 2 starting from the 5th
WEEK (orders.created_at, 4) AS week 

//Results in 1st to 5th for week 0 (range 0-53)
//with week 2 starting from the 6th
WEEK (orders.created_at, 5) AS week 

//Results in 1st to 4th for week 1 (range 1-53)
//with week 2 starting from the 5th
WEEK (orders.created_at, 6) AS week 

//Results in 6th to 12th for week 1 (range 1-53)
//Placed 1st to 5th Jan (what should have been week 1) in week 53!
//with week 2 starting from the 13th
WEEK (orders.created_at, 7) AS week

Is there a more suitable alternative that I should be using instead.

I need to be able to accurately compare the same date periods.

2024 1st-7th Jan, 8th-14th Jan,... etc against 2025 1st-7th Jan, 8th-14th Jan,... etc

Please see SQL below.

SELECT
    YEAR (orders.created_at) AS year,
    WEEK (orders.created_at) AS week,
    min(orders.created_at) as created_date_min,
    max(orders.created_at) as created_date_max,
    sum(
        orders_products.final_price
    ) AS total
FROM
    `orders_products`
INNER JOIN `orders` ON `orders_products`.`order_id` = `orders`.`id`
GROUP BY
    `year`,
    `week`
ORDER BY
    `year` ASC,
    `week` ASC

Update: I've decided against labelling columns 1st-7th Jan as this would be deceptive for leap years. Instead, columns are labelled by week number 1-53.

Solution

If despite Lajos' enlighted plea you still need to have "week 1 = the first 7 days of the year" (= from Jan 1st to Jan 7th),
just replace your week by:

(DAYOFYEAR (orders.created_at) - 1) DIV 7 + 1 AS week

that is: the week that you wish is simply the euclidian divide of the day of the year by 7 (with adjustement of - 1 … + 1 because mathematics start at 0 while days start at 1).

However beware that (due to reasons that Lajos longly exposed) you will never get your year align on weeks, so you'll have to compromise between:

aligning the years on weeks
which the international standards do, with what you noticed:
1. the last days of 2024 are counted in the first week of 2025, or the first days of 2025 in the last week of 2024
2. some years have 52 weeks, some 53
  (which accordingly is a problem for your need to compare every week to the previous year's corresponding one)
aligning weeks on the years
which you choose, with 2 drawbacks:
1. each year will have a different "first day of week"\
2. the last week of the year will have only 1 or 2 days

You can see the effects in a fiddle that I created.
It is based on your query + the week calculation I showed;
I created data for only the first and last 2 weeks of the year, to see how the extremities behave;
and I put exactly one order with a price of 1.0 on each day, so in practice the total column gives the number of days of the week.

I put a second query in it, where the last two weeks are merged with a LEAST(),
so instead of a forelast week of always 7 days and a last one of 1 or 2 days,
you get a combined last week of either 8 or 9 days:

LEAST(52, (DAYOFYEAR (orders.created_at) - 1) DIV 7 + 1) AS week

Packing it all

Thus your query becomes:

SELECT
    YEAR (orders.created_at) AS year,
    (DAYOFYEAR (orders.created_at) - 1) DIV 7 + 1 AS week, -- Weeks 1 to 53, the last of which has 1 or 2 days, all others have 7.
    /*LEAST(52, (DAYOFYEAR (orders.created_at) - 1) DIV 7 + 1) AS week, -- Weeks 1 to 52, the last of which has 8 or 9 days, all others have 7. */
    min(orders.created_at) as created_date_min,
    max(orders.created_at) as created_date_max,
    sum(
        orders_products.final_price
    ) AS total
FROM
    `orders_products`
INNER JOIN `orders` ON `orders_products`.`order_id` = `orders`.`id`
GROUP BY
    `year`,
    `week`
ORDER BY
    `year` ASC,
    `week` ASC