Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

I found a solution in comments of

SELECT DATE_ADD(day, INTERVAL (7 - DAYOFWEEK(day)) DAY) AS day, AVG(position) AS position FROM table
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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