Feature Request : logbook query to shows hours by aircraft

[joeaudette]

I would like to see a new logbook query to show me how many hours I have in each aircraft.
I was able to write a query for it using external sqlite gui, but wish it could be built in.

SELECT
aircraft_name,
aircraft_type,
SUM(hours_flown) as total_hours
FROM
(
select
logbook_id,
aircraft_name,
aircraft_type,
CAST ((julianday(destination_time) - julianday(departure_time)) * 24 AS REAL) AS hours_flown
from logbook
WHERE
departure_time IS NOT NULL
AND destination_time IS NOT NULL
)
GROUP BY
aircraft_name,
aircraft_type
ORDER BY total_hours DESC
;

[albar965]

Makes sense since the other queries cannot be sorted by traveling time.
Thanks a lot for the query. 👍

[albar965]

Fixed (and much more) with 5dcb3ba

[joeaudette]

I wish I had thought of it sooner, but it is trivial to also include count of flights per aircraft in the same query.

SELECT aircraft_name, aircraft_type, SUM(hours_flown) as total_hours, COUNT(*) AS total_flights
FROM (select logbook_id, aircraft_name, aircraft_type,
CAST ((julianday(destination_time) - julianday(departure_time)) * 24 AS REAL) AS hours_flown
from logbook
WHERE departure_time IS NOT NULL AND destination_time IS NOT NULL)
GROUP BY
aircraft_name,
aircraft_type
ORDER BY total_hours DESC
;

[albar965]

No problem. Still fixing small bugs here and there anyway. 🙂

[joeaudette]

I promise, this is the last one, but this occurred to me tonight and seems very nice to have, last_flight and first_flight per aircraft.
Note it requires adding departure_time to the sub query in main from clause.

SELECT aircraft_name, aircraft_type, SUM(hours_flown) as total_hours, COUNT(*) AS total_flights, MAX(departure_time) AS last_flight, MIN(departure_time) AS first_flight
FROM (select logbook_id, aircraft_name, aircraft_type, departure_time,
CAST ((julianday(destination_time) - julianday(departure_time)) * 24 AS REAL) AS hours_flown
from logbook
WHERE departure_time IS NOT NULL AND destination_time IS NOT NULL)
GROUP BY
aircraft_name,
aircraft_type
ORDER BY total_hours DESC
;

[joeaudette]

maybe avg_flt_hours is also interesting..

SELECT aircraft_name, aircraft_type, SUM(hours_flown) as total_hours, COUNT(*) AS total_flights, AVG(hours_flown) as avg_flt_hours, MAX(departure_time) AS last_flight, MIN(departure_time) AS first_flight
FROM (select logbook_id, aircraft_name, aircraft_type, departure_time,
CAST ((julianday(destination_time) - julianday(departure_time)) * 24 AS REAL) AS hours_flown
from logbook
WHERE departure_time IS NOT NULL AND destination_time IS NOT NULL)
GROUP BY
aircraft_name,
aircraft_type
ORDER BY total_hours DESC
;

[joeaudette]

total_distance and avg_distance are also easy to include

SELECT aircraft_name, aircraft_type,
COUNT(*) AS total_flights,
SUM(hours_flown) as total_hours,
AVG(hours_flown) as avg_hours,
SUM(distance_flown) AS total_distance,
AVG(distance_flown) AS avg_distance,
MAX(departure_time) AS last_flight,
MIN(departure_time) AS first_flight
FROM (select logbook_id, aircraft_name, aircraft_type, departure_time, ifnull(distance_flown,0) AS distance_flown,
CAST ((julianday(destination_time) - julianday(departure_time)) * 24 AS REAL) AS hours_flown
from logbook
WHERE departure_time IS NOT NULL AND destination_time IS NOT NULL)
GROUP BY
aircraft_name,
aircraft_type
ORDER BY total_hours DESC
;

[albar965]

Good I made the stats code more flexible. 🙂
I can add these too. No problem. Still reorganizing code and fixing bugs.