CODEWITHSUNDEEP
mysql
user492642
user492642

Reputation: 169

Do the sum weekly in MySQL

I have a table with some date and some values and I want to do the sum by week.

I use this query to do that

SELECT SUM(nb_like) AS nb_like , CONCAT(date, '-', date + INTERVAL 6 DAY) AS week
FROM fb_stats
GROUP BY WEEK(date)
ORDER BY WEEK(date)

the problem is: The sum is not correct (if I do the sum from 2016-01-16 to 2016-01-22 [![enter image description here][1]][1])and I don't know why the start day is not correct

Upvotes: 2

Views: 4096

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

You are grouping by WEEK(date), so as to get one result row per week. The expression

CONCAT(date, '-', date + INTERVAL 6 DAY)

however is not an aggregate (like SUM, MAX or the like). So the date in this expression is just one of the dates found in the records of the week arbitrarily picked.

What you want instead is the week's first day. In order to do so make sure first that you use an appropriate mode for the WEEK function (or rather YEARWEEK in order not to mix the years) to ensure there exists that day in every week selected, e.g:

YEARWEEK(date, 2)

which gets you weeks starting with Sunday. Mode 7 would get you week starting with Monday instead. Read up un this here: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_week.

Then use this method to get the Sunday from the week:

STR_TO_DATE('<yearweek> Sunday', '%X%V %W');

Replace <yearweek> with the real yearweek. (And replace 'Sunday' with 'Monday' if you decide for mode 7.)

The complete query:

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);

Upvotes: 5

Related Questions