gistfile1.sql

create view counts_per_year
as SELECT pilot, substring(filename, 14, 4) as year, count(detail) as pocet
FROM test.output 
where aircraft like 'ventus%'
group by pilot, year;

select B.pilot, 
	IFNULL(O1.pocet, 0) as `2014`, 
    IFNULL(O2.pocet, 0) as `2015`, 
    IFNULL(O2.pocet, 0) - IFNULL(O1.pocet, 0) as delta,
    IFNULL(O2.pocet, 0) / IFNULL(O1.pocet, 1) as ratio
from counts_per_year as B
left join counts_per_year as O1 on B.pilot=O1.pilot and O1.year='2014'
left join counts_per_year as O2 on B.pilot=O2.pilot and O2.year='2015'
group by B.pilot