I have a query which I use to grab specific dates and a price for the date, but now I'd like to use something similar to grab the avg prices for particular days of the week.

Here's my current query which works for specific dates to pull from a table called availables:

SELECT rooms.name, rooms.roomtype, rooms.id, max(availables.updated_at),
MAX(IF(to_days(availables.bookdate) - to_days('2009-12-10') = 0, (availables.price*0.66795805223432), '')) AS day1,
MAX(IF(to_days(availables.bookdate) - to_days('2009-12-10') = 1, (availables.price*0.66795805223432), '')) AS day2,
MAX(IF(to_days(availables.bookdate) - to_days('2009-12-10') = 2, (availables.price*0.66795805223432), '')) AS day3,
MAX(IF(to_days(availables.bookdate) - to_days('2009-12-10') = 3, (availables.price*0.66795805223432), '')) AS day4,
MAX(IF(to_days(availables.bookdate) - to_days('2009-12-10') = 4, (availables.price*0.66795805223432), '')) AS day5,
MAX(IF(to_days(availables.bookdate) - to_days('2009-12-10') = 5, (availables.price*0.66795805223432), '')) AS day6,
MAX(IF(to_days(availables.bookdate) - to_days('2009-12-10') = 6, (availables.price*0.66795805223432), '')) AS day7,
MIN(spots) as spots
     FROM `availables`
     INNER JOIN rooms
     ON availables.room_id=rooms.id
     WHERE rooms.hotel_id = '5064' AND bookdate
     BETWEEN '2009-12-10' AND DATE_ADD('2009-12-10', INTERVAL 6 DAY)
     GROUP BY rooms.name
     ORDER BY rooms.ppl

My first stab which doesn't work, probably because the DAYSOFWEEK function is much different from the to_days...

SELECT rooms.id, rooms.name,
MAX(IF(DAYOFWEEK(availables.bookdate) - DAYOFWEEK('2009-12-10') = 0, (availables.price*0.66795805223432), '')) AS day1,
MAX(IF(DAYOFWEEK(availables.bookdate) - DAYOFWEEK('2009-12-10') = 1, (availables.price*0.66795805223432), '')) AS day2,
MAX(IF(DAYOFWEEK(availables.bookdate) - DAYOFWEEK('2009-12-10') = 2, (availables.price*0.66795805223432), '')) AS day3,
MAX(IF(DAYOFWEEK(availables.bookdate) - DAYOFWEEK('2009-12-10') = 3, (availables.price*0.66795805223432), '')) AS day4,
MAX(IF(DAYOFWEEK(availables.bookdate) - DAYOFWEEK('2009-12-10') = 4, (availables.price*0.66795805223432), '')) AS day5,
MAX(IF(DAYOFWEEK(availables.bookdate) - DAYOFWEEK('2009-12-10') = 5, (availables.price*0.66795805223432), '')) AS day6,
MAX(IF(DAYOFWEEK(availables.bookdate) - DAYOFWEEK('2009-12-10') = 6, (availables.price*0.66795805223432), '')) AS day7,rooms.ppl AS spots FROM `availables` 
 INNER JOIN `rooms` ON `rooms`.id = `availables`.room_id 
 WHERE (rooms.hotel_id = 5064 AND rooms.ppl > 3 AND availables.price > 0 AND availables.spots > 1) 
 GROUP BY rooms.name
 ORDER BY rooms.ppl

Maybe i'm making this crazy hard and someone knows a much simpler way.

It takes data that looks like this

#Availables
id    room_id   price    spots    bookdate
1     26        $5       5        2009-10-20
2     26        $6       5        2009-10-21

to:

+----+-------+--------------------+---------------------+---------------------+---------------------+------+------+------+------+
| id | spots | name               | day1                | day2                | day3                | day4 | day5 | day6 | day7 |
+----+-------+--------------------+---------------------+---------------------+---------------------+------+------+------+------+
| 25 | 4     | Blue Room          | 14.9889786921381408 | 14.9889786921381408 | 14.9889786921381408 |      |      |      |      |
| 26 | 6     | Whatever           | 13.7398971344599624 | 13.7398971344599624 | 13.7398971344599624 |      |      |      |      |
| 27 | 8     | Some name          | 11.2417340191036056 | 11.2417340191036056 | 11.2417340191036056 |      |      |      |      |
| 28 | 8     | Another            | 9.9926524614254272  | 9.9926524614254272  | 9.9926524614254272  |      |      |      |      |
| 29 | 10    | Stuff              | 7.4944893460690704  | 7.4944893460690704  | 7.4944893460690704  |      |      |      |      |
+----+-------+--------------------+---------------------+---------------------+---------------------+------+------+------+---