Question 179421 in Mysql Question posted in
The official MySQL documentation can be found here.

Which week does MySQL start counting when using ‘GROUP BY WEEK’?

Lets say I have this query:

select 
week(sr.transactionDate) AS week_number,
sum(sr.digitalEarnings)
from
sales_report as sr
where `sr`.`transactionDate` BETWEEN '2022-05-01' AND '2022-12-31'
group by week_number
order by week_number;

Which returns data like so:

enter image description here

I know this is the "week in the year" number but I’m actually trying to get the data divided into weeks starting from 1. i.e Week 1 in this period, week 2 in this period etc.

How would I achieve this?

Tags:

3

Answers


  1. 0

    how about using ROW_NUMBER() although this is only available in v8 and above:

    https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number

    SELECT 
    week(sr.transactionDate) AS week_number,
    ROW_NUMBER() OVER (GROUP BY week_number ORDER BY week_number) AS week_n,
    sum(sr.digitalEarnings)
 FROM
     sales_report as sr
 WHERE `sr`.`transactionDate` BETWEEN '2022-05-01' AND '2022-12-31'
 GROUP BY week_number
 ORDER BY week_number;
    Login or Signup to reply.
  2. 0

    You could just use some common sense math:

    SELECT week(sr.transactionDate) - MIN(week(sr.transactionDate)) OVER () + 1 AS week_num
FROM ...
WHERE ...
GROUP BY week(sr.transactionDate)

    Or this:

    SELECT week(sr.transactionDate) - week('2022-05-01') + 1 AS week_num
FROM ...
WHERE sr.transactionDate BETWEEN '2022-05-01' ...
GROUP BY week(sr.transactionDate)

    First one will start numbering weeks from 1, the second one has potential of starting from, e.g. 3 if filtered data starts from 2022-05-15. Both queries can have gaps for wholly missing weeks.

    Login or Signup to reply.
  3. 0

    You can calculate week number directly from the transaction date. Assuming you have left the default_week_format system variable unchanged, you are doing weeks starting with Sunday; adjust your range start date to find the Sunday on or before, and count the weeks from that to the transaction date, adding one to start with week 1, not week 0:

    floor(datediff(sr.transactionDate,'2022-05-01' - interval dayofweek('2022-05-01')-1 day) / 7) + 1

    I strongly suggest not using week() at all; its behavior across years or near the beginning or end of years is usually not friendly for reporting purposes. Especially subtracting or comparing two week values can be problematic.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search