Stat generator

From Openflyers

Jump to: navigation, search

Introduction

The goal of this page is to propose a list of statistic generation queries (SQL)

Parameters

$year : whatever the year you want in YYYY format (for example 2007, 1867, ...).

French administration Examples

  • Flights hours total per aircraft per year 
 SELECT 
   callsign AS Callsign, 
   YEAR( start_date ) AS Year, 
   CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total 
 FROM flight 
   LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id 
 WHERE YEAR(start_date) = $year 
 GROUP BY callsign 
 UNION 
 SELECT 
   "Total", 
   $year AS year, 
   CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total      
 FROM flight 
 WHERE YEAR(start_date) = $year 
 GROUP BY year
  • Flights hours total per aircraft per year and per month 
 SELECT  aircraft_id AS Num, callsign, 
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1 AND aircraft_id = Num) AS Janu,
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2 AND aircraft_id = Num) AS Febr,
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3 AND aircraft_id = Num) AS Marc,
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4 AND aircraft_id = Num) AS Apri,
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5 AND aircraft_id = Num) AS May,
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6 AND aircraft_id = Num) AS June,
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7 AND aircraft_id = Num) AS July,
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8 AND aircraft_id = Num) AS Augu,
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9 AND aircraft_id = Num) AS Sept,
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10 AND aircraft_id = Num) AS Octo,
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11 AND aircraft_id = Num) AS Nove,
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12 AND aircraft_id = Num) AS Dece,
 CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Sum 
 FROM flight
 LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id 
 WHERE YEAR(start_date)= $year  AND aircraft.non_flightable = 0 
 GROUP BY order_num
 UNION 
 SELECT  "Sum per","month", 
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 1),
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 2),
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 3),
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 4),
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 5),
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 6),
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 7),
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 8),
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 9),
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 10),
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 11),
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)= 12),
 CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total
 FROM flight
 LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id 
 WHERE YEAR(start_date)= $year AND aircraft.non_flightable = 0 
 UNION 
 SELECT  "Cumulative","per month", 
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 1),
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 2),
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 3),
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 4),
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 5),
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 6),
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 7),
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 8),
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 9),
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 10),
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 11),
 (SELECT CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) FROM flight WHERE YEAR(start_date)= $year AND MONTH(start_date)<= 12),
 $year
 FROM flight
 LEFT JOIN aircraft ON aircraft.id = flight.aircraft_id 
 WHERE YEAR(start_date)= $year  AND aircraft.non_flightable = 0
  • Number of members : male, female, less than 21 years and more than 21 years 
 SELECT
   IF ( (sex = 0), 'Men', 'Women' ) AS Sex, 
   IF( ( $year - YEAR( birthdate ) >= 21), 'No', 'Yes') AS Young, 
   COUNT( id ) AS Number  
 FROM authentication 
 WHERE activated='1' 
 GROUP BY 
   Sex,
   Young
  • Flights hours : less than 21 years, more than 21 years, male, female 
 SELECT 
   IF( a.sex=0, 'Men','Women') AS Sexe,
   IF( ( $year - YEAR( birthdate ) >= 21) , 'No', 'Yes') AS Young,
   IF ((SELECT COUNT(*) 
        FROM flight_pilot fp2 
        WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0, 'Solo', 'double') AS DC, 
  CONCAT(FLOOR(SUM( flight.duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight.duration )/600 - FLOOR(SUM( flight.duration )/600))*3600),'%i')) AS Total 
 FROM flight_pilot fp 
   LEFT JOIN flight ON fp.flight_id=flight.id 
   LEFT JOIN authentication a ON a.id=fp.pilot_id 
 WHERE YEAR( start_date ) = $year 
   AND fp.num=0 
 GROUP BY 
   Sex,
   Young,
   DC
 UNION 
 SELECT 
   IF ( (sex = 0), 'Male', 'Female' ) AS Sex,"All",
   IF ( num = 0, 'Total', 'DC') AS DC,
   CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total 
 FROM flight 
   LEFT JOIN flight_pilot fp ON fp.flight_id=flight.id 
   LEFT JOIN authentication a ON a.id=fp.pilot_id 
 WHERE fp.num=0 AND YEAR(start_date)=$year 
 GROUP BY Sex
 UNION 
 SELECT 
   "All",
   IF( ( $year - YEAR( birthdate ) >= 21) , 'No', 'Yes') AS Young,
   IF ( num = 0, 'Total', 'DC') AS DC,
   CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total 
 FROM flight 
   LEFT JOIN flight_pilot fp ON fp.flight_id=flight.id 
   LEFT JOIN authentication a ON a.id=fp.pilot_id  
 WHERE YEAR( start_date ) = $year 
   AND fp.num=0 
 GROUP BY Young 
 UNION 
 SELECT 
   "Total", 
   $year AS year, 
   IF ( num = 0, 'Total', 'DC') AS DC,
   CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total 
 FROM flight 
   LEFT JOIN flight_pilot fp ON fp.flight_id=flight.id 
   LEFT JOIN authentication a ON a.id=fp.pilot_id 
 WHERE YEAR( start_date ) = $year 
   AND fp.num=0 
 GROUP BY year
  • Flights hours following nationality 
  SELECT  
    nationality.label, 
    SUM( duration ) AS total
  FROM flight , flight_pilot fp, authentication a, nationality
  WHERE flight.id = fp.flight_id
    AND fp.pilot_id = a.id
    AND a.nationality_id = nationality.id
  GROUP BY nationality_id
  • Licence in the year 
 SELECT 
   last_name AS Last_name, 
   first_name AS First_name, 
   IF( ( $year - YEAR( birthdate ) >= 21) , 'No', 'Yes') AS Young,
   IF ( (sex = 0), 'Male', 'Female' ) AS Sex, DATE_FORMAT( grant_date, '%d/%m/%Y' ) AS Date, qualification.name AS Qualif 
 FROM authentication 
   LEFT JOIN member_qualif ON member_id=authentication.id 
   LEFT JOIN qualification ON qualification.id=member_qualif.qualif_id 
 WHERE YEAR(grant_date)=$year
  • Learning pilots 
 SELECT 
   last_name AS Last_name,
   first_name AS First_name, 
   qualification.name AS Qualification, 
   member_qualif.expire_date AS Expirate 
 FROM authentication 
   LEFT JOIN member_qualif ON member_id=authentication.id 
   LEFT JOIN qualification ON qualification.id=qualif_id 
 WHERE grant_date IS NULL 
   AND qualification.grant_date_enable=1 
   AND qualification.name="Learning pilot" 
   AND authentication.activated=1 
 ORDER BY 
   last_name,
   first_name
  • Young list 
 SELECT 
   last_name AS Last_name,
   first_name AS First_name, 
   birthdate AS Birthdate, 
   IF ( (sex = 0), 'Male', 'Female' ) AS sex
 FROM authentication 
 WHERE ($year-YEAR(birthdate))<=21
  • Flights hours total per flight type 
 SELECT 
   ft.name AS Type_vol,
   IF ((SELECT
          COUNT(*) 
        FROM flight_pilot fp2 
        WHERE fp2.flight_id=fp.flight_id  
          AND fp2.num=1)=0, 'Solo', 'Double') AS DC,
   CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total 
 FROM flight 
   LEFT JOIN flight_type ft ON ft.id & flight.flight_type_id 
   LEFT JOIN flight_pilot fp ON fp.flight_id=flight.id 
 WHERE YEAR( start_date ) = $year 
   AND fp.num=0 
 GROUP BY 
   ft.id, 
   DC HAVING (Name <> 'Instruction' OR DC <> 'Double') 
 ORDER BY order_num ASC
  • Number of landing or take off outside 
 SELECT 
   ICAO, 
   name AS Name, 
   COUNT( icao ) AS nb_visite 
 FROM flight f, icao ap 
 WHERE (f.airfield_departure = ap.icao OR f.airfield_arrival = ap.icao) 
    AND airfield_departure != airfield_arrival 
    AND YEAR( start_date ) = $year 
 GROUP BY icao 
 ORDER BY nb_visite DESC

Nota : total mouvments on based platform is preceding result + number of visit to ICAO based platform

  • Number of take-off and landings on flying club airfield 
 SELECT 
   SUM( landing_number )*2 AS nb_mouvement 
 FROM flight f, club c 
 WHERE f.airfield_departure = c.icao 
   AND f.airfield_arrival = c.icao 
   AND YEAR( start_date ) = $year
  • Flights hours total per instructor 
 SELECT 
   last_name AS Last_name,
   first_name AS First_name, 
   CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total 
 FROM flight 
   LEFT JOIN flight_pilot fp ON fp.flight_id = flight.id 
   LEFT JOIN authentication a ON a.id=fp.pilot_id 
   LEFT JOIN instructor i ON i.id = a.id 
 WHERE i.id = fp.pilot_id 
   AND fp.num = 1 
   AND YEAR( start_date ) = $year  
 GROUP BY a.id 
 UNION 
 SELECT 
   "Total", 
   $year AS year, 
   CONCAT(FLOOR(SUM( duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( duration )/600 - FLOOR(SUM( duration )/600))*3600),'%i')) AS Total 
 FROM flight,flight_pilot fp, instructor i 
 WHERE flight.id = fp.flight_id 
   AND fp.pilot_id = i.id 
   AND fp.num = 1 
   AND YEAR( start_date ) = $year 
 GROUP BY year
  • Flights hours total per pilot 
 SELECT 
   CONCAT(last_name,' ',first_name) AS Nom, 
   IF (( SELECT 
           COUNT(*) 
         FROM flight_pilot fp2 
         WHERE fp2.flight_id=fp.flight_id AND fp2.num=1)=0,
     'Solo',
     'double') AS Solo_DC, 
   CONCAT(FLOOR(SUM( flight.duration )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight.duration )/600 - FLOOR(SUM( flight.duration )/600))*3600),'%i')) AS Total 
 FROM flight_pilot fp 
 LEFT JOIN flight ON fp.flight_id=flight.id 
 LEFT JOIN authentication a ON a.id=fp.pilot_id 
 WHERE YEAR( start_date ) = $year AND fp.num=0 
 GROUP BY Nom, Solo_DC
  • Member list by pool 
 SELECT
   last_name AS Lastname,
   first_name AS Firstname,
   DATE_FORMAT(subscription, '%d %m %Y' ) AS Date,
   member_pool.name AS Pool
 FROM authentication
   LEFT JOIN member ON member.id = authentication.id
   LEFT JOIN member_pool_join ON member_pool_join.member_id = member.id
   LEFT JOIN member_pool ON member_pool.id = member_pool_join.member_pool_id
 WHERE YEAR(subscription) >= $year
 ORDER BY Pool, Lastname, Firstname
Retrieved from "http://wiki.openflyers.org/index.php/Stat_generator"
Personal tools