Skip to content

NTSB investigations: Aero Commander 500-B

80 cases on aircraft the registry lists as Aero Commander 500-B. Aircraft are matched to the registry by N-number and event date, checked against the reported serial number, make and model, so older matches can be wrong.

Defining event

Finding

Phase of flight

Aircraft model

Cases, newest first

Query with SQL

SELECT c."ntsbNumber", c."eventDate", c."eventType", c."eventCity", c."eventStateOrRegion", a.n_number, a."aircraftMake", a."aircraftModel", c."highestInjuryLevel", c."totalFatal", c."completionStatus", c.report_url FROM 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' c LEFT JOIN 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' a ON a.case_mkey = c."mKey" AND a.position = 1 WHERE c."mKey" IN (SELECT x.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' x WHERE x.aircraft_model_code IN ('0141106')) ORDER BY c."eventDate" DESC, c."ntsbNumber" DESC

duckdb -csv -c "SELECT c.\"ntsbNumber\", c.\"eventDate\", c.\"eventType\", c.\"eventCity\", c.\"eventStateOrRegion\", a.n_number, a.\"aircraftMake\", a.\"aircraftModel\", c.\"highestInjuryLevel\", c.\"totalFatal\", c.\"completionStatus\", c.report_url
  FROM 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' c LEFT JOIN 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' a ON a.case_mkey = c.\"mKey\" AND a.position = 1
  WHERE c.\"mKey\" IN (SELECT x.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' x WHERE x.aircraft_model_code IN ('0141106'))
  ORDER BY c.\"eventDate\" DESC, c.\"ntsbNumber\" DESC"

Prints the whole list as CSV. It reads the release’s Parquet files over HTTPS, so there’s nothing to download first. Install DuckDB to run it.

SELECT c."ntsbNumber", c."eventDate", c."eventType", c."eventCity", c."eventStateOrRegion", a.n_number, a."aircraftMake", a."aircraftModel", c."highestInjuryLevel", c."totalFatal", c."completionStatus", c.report_url
FROM 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' c LEFT JOIN 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' a ON a.case_mkey = c."mKey" AND a.position = 1
WHERE c."mKey" IN (SELECT x.case_mkey FROM 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' x WHERE x.aircraft_model_code IN ('0141106'))
ORDER BY c."eventDate" DESC, c."ntsbNumber" DESC

Run it at the DuckDB prompt, or in any tool that reads Parquet over HTTPS. More ways to query the release are on Use the data.

NTSB DEN76FTE23Accident · Minor injuriesClosedAero Comdr 500B · Akron, COAug 29, 1975 · N77JJ · Substantial damage NTSB MIA76AM007Accident · 2 fatalClosedAero Comdr 500B · Red Hill, GAJul 22, 1975 · N181Z · Destroyed NTSB IAD75FLQ11Accident · Serious injuriesClosedAero Comdr 500B · Paducah, KYDec 29, 1974 · N553HH · Substantial damage NTSB CHI74FEK09Accident · No injuriesClosedAero Comdr 500B · Findly, OHDec 9, 1973 · N354MA · Substantial damage NTSB DEN73FQA43Accident · No injuriesClosedAero Cmdr 500-B · Crownpoint, NMJun 1, 1973 · N160X · Substantial damage NTSB MKC73DCG39Accident · No injuriesClosedAero Comdr 500-B · Joplin, MOApr 21, 1973 · N2001 · Substantial damage NTSB MIA73AM082Accident · 2 fatalClosedAero Comdr 500B · Alcoa, TNFeb 8, 1973 · N6166X · Destroyed NTSB MIA72AM113Accident · 5 fatalClosedAero Comdr 500-B · College Park, GAApr 22, 1972 · N6319U · Destroyed NTSB NYC72AN106Accident · 1 fatalClosedAero Comdr 500-B · Rochester, NYMar 3, 1972 · N6298X · Destroyed NTSB MKC72DCD40Accident · No injuriesClosedAero Comdr 500-B · Waterloo, IAOct 18, 1971 · N553HH · Substantial damage NTSB CHI72AC006Accident · 2 fatalClosedAero Comdr 500-B · No.Madison, OHJul 15, 1971 · N707CA · Destroyed NTSB FTW71AF084Accident · No injuriesClosedAero Comdr 500B · Crossett, ARJun 21, 1971 · N6210X · Substantial damage NTSB MIA71AM060Accident · 2 fatalClosedAero Comdr 500B · Jackson, TNJan 12, 1971 · N6254X · Destroyed NTSB NYC71AN043Accident · 2 fatalClosedAero Comdr 500-B · Mahaffey, PAOct 4, 1970 · N701X · Destroyed NTSB MIA70FKJ41Accident · No injuriesClosedAero Comdr 500-B · Clanton, ALJun 15, 1970 · N4517E · Destroyed NTSB OAK70F0211Accident · Serious injuriesClosedAero Comdr 500-B · Montague, CAJul 21, 1969 · N890CP · Substantial damage NTSB MKC69I0060Incident · No injuriesClosedAero Comdr 500-B · Iowa City, IAMay 22, 1969 · N6308U · No damage NTSB MKC69D0339Accident · No injuriesClosedAero Comdr 500-B · Waterloo, IAJan 27, 1969 · N553HH · Substantial damage NTSB SEA69D0174Accident · No injuriesClosedAero Comdr 500-B · Belgrade, MTJan 8, 1969 · N4918 · Substantial damage NTSB SEA69A0036Accident · 1 fatalClosedAero Comdr 500-B · Imnaha, ORDec 18, 1968 · N5080 · Destroyed NTSB SEA69A0035Accident · 1 fatalClosedAero Comdr 500 · Glendive, MTDec 18, 1968 · N6281X · Destroyed NTSB FTW68A0067Accident · Minor injuriesClosedAero Cmdr 500-B · Mena, ARDec 30, 1967 · N6369U · Substantial damage NTSB FTW68O0001Accident · No injuriesClosedAero Comdr 500B · Mulege, MexicoJul 2, 1967 · N6172X NTSB MIA68D0018Accident · No injuriesClosedAero Comdr 500-B · Huntsville, ALJun 23, 1967 · N6194X · Substantial damage NTSB NYC67F0193Accident · No injuriesClosedAero Cmdr 500-B · Elkins, WVAug 16, 1966 · N2001 · Substantial damage NTSB FTW66F0313Accident · No injuriesClosedAero Cmdr 500B · Addison, TXSep 27, 1965 · N353CC · Substantial damage NTSB LAX66D0047Accident · No injuriesClosedAero Cmdr 500B · Chino, CAJul 5, 1965 · N6390U · Substantial damage NTSB LAX66D0050Accident · No injuriesClosedAero Cmdr 500B · Corona, CAJun 8, 1965 · N6390U · Substantial damage NTSB CHI65A0093Accident · 2 fatalClosedAero Comm 500B · Rockford, ILApr 29, 1965 · N78333 · Destroyed NTSB UNK64X1234Accident · No injuriesClosedAero Comm 500B · Elizabeth City, NCApr 9, 1964 · N6224X · Substantial damage