SELECT
    COUNT(flights.flight_id) AS flights_amount,
    CASE
        WHEN aircrafts.model LIKE '%Boeing%' THEN 'Boeing'
        WHEN aircrafts.model LIKE '%Airbus%'  THEN 'Airbus'
        ELSE 'other'
    END AS type_aircraft
FROM
    aircrafts
    INNER JOIN flights ON aircrafts.aircraft_code = flights.aircraft_code
WHERE
    CAST(flights.departure_time AS date) >= '2018-09-01' AND
    CAST(flights.departure_time AS date) < '2018-10-01'
GROUP BY
    type_aircraft;