mysql sum per week 1

mysql sum per week

SELECT 
  SUM(nb_like) AS nb_like, 
  CONCAT
  (
    STR_TO_DATE(CONCAT(YEARWEEK(date, 2), ' Sunday'), '%X%V %W'),
    '-',
    STR_TO_DATE(CONCAT(YEARWEEK(date, 2), ' Sunday'), '%X%V %W') + INTERVAL 6 DAY
  ) AS week
FROM fb_stats
GROUP BY YEARWEEK(date, 2)
ORDER BY YEARWEEK(date, 2);

Here is what the above code is Doing:
1. We’re using the YEARWEEK() function to get the week number of the year.
2. We’re using the CONCAT() function to concatenate the year and week number with the string ‘ Sunday’ to get the first day of the week.
3. We’re using the STR_TO_DATE() function to convert the string to a date.
4. We’re using the INTERVAL keyword to add 6 days to the first day of the week to get the last day of the week.
5. We’re using the GROUP BY clause to group the rows by week.
6. We’re using the ORDER BY clause to sort the rows by week.

Similar Posts