1

In continuation of my previous question: Get week/month data

I have a MySQL table with day column of type DATE and position column of type INT.

I need to fetch average position for every week in consideration. (A week is in consideration if there is a row with day value belonging to this week.)

The fetched data should be an array of two columns:

  • date
  • position

Here "date" is the first day (Sunday) of the week and "position" is the average position for this week.

The trouble is how to get the first day for a week.

Improve this question

1 Answer 1

Reset to default
1

I found a solution in comments of http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

SELECT DATE_ADD(day, INTERVAL (7 - DAYOFWEEK(day)) DAY) AS day, AVG(position) AS position FROM table
Improve this answer
1
  • This gives the last day of the week. To get the first day of the week you need to do DATE(day - INTERVAL (DAYOFWEEK(day) - 1) DAY). If you want the first monday, subtract 2 instead of 1.
    – tvanc
    Commented Mar 16, 2018 at 18:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.