Get last 5 week data and if one week contain more than one record then make it average

Vote count:

0

I have one table which contain history.

id  user_id,     weight,    created_date
1   '10',        '100',     '2014-10-01'
2   '10',        '102',     '2014-10-04'
3   '10',        '104',     '2014-10-08'
4   '10',        '106',     '2014-10-16'
5   '10',        '110',     '2014-10-20'
6   '11',        '120',     '2014-10-20'

now, what i am trying to achive is get last 5 week data and if one week contain more than one records then make it average. how to do that?

I use following query to get last 5 week data:

SELECT 
    weight,created_date
FROM
    tbl1
where
    user_id = 24
    and 
    created_date BETWEEN CURDATE()-INTERVAL 5 WEEK AND CURDATE()
    order by created_date

example: today's date: 2014-11-03

when i fire above query then it returns these output (id: 1,2,3,4,5)

id weight
1   100 
2   102
3   104 
4   106
5   110

then record (id:1 and id:2) is come in one week, multiple records in one week (means between 2014-09-29 to 2014-10-05), so make it avgerage.

expected output:

weight
101  => (average of id: 1 and 2 means (100+102)/2 =101)
104
106
110

so how to achive this? Thanks in advance.

Note: last 5 week calculated from today's date. In above example last 5 weeks are (2014-09-29 to 2014-10-05, 2014-10-06 to 2014-10-12,2014-10-13 to 2014-10-19,....till today)

asked 3 mins ago

DS9

1,069

Get last 5 week data and if one week contain more than one record then make it average