1SELECT 
2  CASE 
3    WHEN LOWER("type") LIKE '%boeing%' THEN 'Boeing'
4    WHEN LOWER("type") LIKE '%airbus%' THEN 'Airbus'
5    WHEN LOWER("type") LIKE '%lockheed%' THEN 'Lockheed'
6    WHEN LOWER("type") LIKE '%fairchild%' THEN 'Fairchild'
7    WHEN LOWER("type") LIKE '%douglas%' THEN 'Douglas'
8    WHEN LOWER("type") LIKE '%mcdonnel%' THEN 'McDonnel'
9    WHEN LOWER("type") LIKE '%canadair%' THEN 'Canadair'
10    ELSE 'Other'
11  END as "Manufacturer",
12  COUNT(*) as "Number of Crashes",
13  AVG("aboard") as "Average aboard",
14  AVG("ground") as "Average killed in ground",
15  AVG("fatalities") as "Average Number of Fatalities",
16  AVG("survivalrate") as "Survival Rate"
17FROM 
18  @kaggle.juancarlosventosa_large_passenger_plane_crashes_19332009.large_passenger_plane_crashes_1933_to_2009
19GROUP BY 
20  CASE 
21    WHEN LOWER("type") LIKE '%boeing%' THEN 'Boeing'
22    WHEN LOWER("type") LIKE '%airbus%' THEN 'Airbus'
23    WHEN LOWER("type") LIKE '%lockheed%' THEN 'Lockheed'
24    WHEN LOWER("type") LIKE '%fairchild%' THEN 'Fairchild'
25    WHEN LOWER("type") LIKE '%douglas%' THEN 'Douglas'
26    WHEN LOWER("type") LIKE '%mcdonnel%' THEN 'McDonnel'
27    WHEN LOWER("type") LIKE '%canadair%' THEN 'Canadair'
28    ELSE 'Other'
29  END
30ORDER BY 
31  "Number of Crashes" DESC;
32