MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

WITH appointment AS (

SELECT 1 AS id, '2018-12-02' AS date, '10:00:00' AS time UNION ALL

SELECT 2, '2018-12-05', '12:00:00' UNION ALL

SELECT 3, '2018-12-12', '16:00:00' UNION ALL

SELECT 4, '2018-12-12', '17:00:00' UNION ALL

SELECT 5, '2018-12-09', '09:00:00'

appointment_services AS (

SELECT 1 AS id, 1 AS appointment_id, 24 AS service_id UNION ALL

SELECT 2, 2, 24 UNION ALL

SELECT 3, 3, 21 UNION ALL

SELECT 4, 4, 24 UNION ALL

SELECT 5, 5, 18

)

SELECT

s.service_id,

COUNT(a.id) AS times

FROM appointment_services s

LEFT JOIN

(

SELECT id

FROM appointment

WHERE date BETWEEN '2018-12-10' AND '2018-12-18'

) a

ON s.appointment_id = a.id

GROUP BY

s.service_id;