SELECT 
  CASE 
    WHEN LOWER("type") LIKE '%boeing%' THEN 'Boeing'
    WHEN LOWER("type") LIKE '%airbus%' THEN 'Airbus'
    WHEN LOWER("type") LIKE '%lockheed%' THEN 'Lockheed'
    WHEN LOWER("type") LIKE '%fairchild%' THEN 'Fairchild'
    WHEN LOWER("type") LIKE '%douglas%' THEN 'Douglas'
    WHEN LOWER("type") LIKE '%mcdonnel%' THEN 'McDonnel'
    WHEN LOWER("type") LIKE '%canadair%' THEN 'Canadair'
    ELSE 'Other'
  END as "Manufacturer",
  COUNT(*) as "Number of Crashes",
  AVG("aboard") as "Average aboard",
  AVG("ground") as "Average killed in ground",
  AVG("fatalities") as "Average Number of Fatalities",
  AVG("survivalrate") as "Survival Rate"
FROM 
  @kaggle.juancarlosventosa_large_passenger_plane_crashes_19332009.large_passenger_plane_crashes_1933_to_2009
GROUP BY 
  CASE 
    WHEN LOWER("type") LIKE '%boeing%' THEN 'Boeing'
    WHEN LOWER("type") LIKE '%airbus%' THEN 'Airbus'
    WHEN LOWER("type") LIKE '%lockheed%' THEN 'Lockheed'
    WHEN LOWER("type") LIKE '%fairchild%' THEN 'Fairchild'
    WHEN LOWER("type") LIKE '%douglas%' THEN 'Douglas'
    WHEN LOWER("type") LIKE '%mcdonnel%' THEN 'McDonnel'
    WHEN LOWER("type") LIKE '%canadair%' THEN 'Canadair'
    ELSE 'Other'
  END
ORDER BY 
  "Number of Crashes" DESC;