By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE bookings
(`ID` int, `arrival_date` date, `departure_date` date, `price_per_night` int)
;
INSERT INTO bookings
(`ID`, `arrival_date`, `departure_date`, `price_per_night`)
VALUES
(1, '2023-01-29")', '2023-02-02', 50)
;
SELECT MONTH(arrival_date), YEAR(arrival_date), SUM(
CASE WHEN DATEDIFF(departure_date,arrival_date) > 1 THEN
(DATEDIFF(departure_date,arrival_date) - 1)
ELSE 1 END * `price_per_night`) sum_price
FROM `bookings`
GROUP BY MONTH(arrival_date), YEAR(arrival_date)
| MONTH(arrival_date) | YEAR(arrival_date) | sum_price |
|---|---|---|
| 1 | 2023 | 150 |