MySQL的每周价格

Question

I have following table structure

id, startdate, hotelid, price

On front-end I am showing min and max price as $35 - $671 (per day) This is min and max price of the hotel per day. I can fetch the per day data based on GROUP BY hotelid and getting min/max price as below:

SELECT
    hotelid,
    MIN(price) AS `minprice`,
    MAX(price) AS `maxprice`
FROM
    hotel_price
WHERE
    startdate > NOW()
    AND
    price > 0
GROUP BY
    hotelid;

Now, customer wants to show per week price as min/max. But I am confused because I have data as per day basis in table, how should I calculate and show weekly SUM of price as min max? How we can do this in a mysql query?

Answer 1

try in where

WHERE price > 0 and startdate BETWEEN NOW() and DATE_SUB(NOW(), INTERVAL 7 DAY)