Getting the first day of week

Question

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.

Answer 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