SQLize Online / PHPize Online  /  SQLtest Online

SELECT day_of_week, rentals_count, SUM(rentals_count) OVER (ORDER BY d_of_w_numb ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS day_rate, IFNULL(SUM(rentals_count) OVER (ORDER BY d_of_w_numb ROWS BETWEEN CURRENT ROW AND 1 PRECEDING), rentals_count) AS examp FROM (SELECT DATE_FORMAT(rental_date, "%W") AS day_of_week, DATE_FORMAT(rental_date, "%w") AS d_of_w_numb, COUNT(*) AS rentals_count FROM rental WHERE DATE_FORMAT(rental_date, "%w") <> 0 GROUP BY DATE_FORMAT(rental_date, "%W"), DATE_FORMAT(rental_date, "%w") UNION ALL SELECT DATE_FORMAT(rental_date, "%W") AS day_of_week, 7 AS d_of_w_numb, COUNT(*) AS rentals_count FROM rental WHERE DATE_FORMAT(rental_date, "%w") = 0 GROUP BY DATE_FORMAT(rental_date, "%W"), DATE_FORMAT(rental_date, "%w") ORDER BY d_of_w_numb) dd