I will guess in advance that I'm missing something obvious, but I
*think* I need a subselect here and I've never done those. So if anyone
has a comment that will point me in the right direction, I'd appreciate
it. The short version is that I am trying to create a summary table by
month, and for all but one criterion I've done it. This is my first
select query:
select to_char(date, 'YYYY-MM') as month, sum(hrs_dual) as hrs_dual,
sum(hrs_pic) as hrs_pic, sum(hrs_night) as hrs_night, sum(hrs_ifrsim)
as hrs_ifrsim, sum(hrs_ifract) as hrs_ifract, sum(apps_ifrsim) as
apps_ifrsim, sum(apps_ifract) as apps_ifract, sum(hrs_total) as
hrs_total from flights group by month order by month asc;
If that's a pain to read, it could be simplified to:
select to_char(date, 'YYYY-MM') as month, sum(hrs_total) as hrs_total
from flights group by month order by month asc;
This query allows me to get my total monthly flights as pilot-in-
command, at night, and by other criteria which are stored as columns in
the flights table. However, I have one criterion which depends on
another table, and that is multi-engine time. This can be found via the
following select query:
simpatic_logbook=> select to_char(date, 'YYYY-MM') as month,
sum(hrs_total) from flights,aircraft where
flights.callsign=aircraft.callsign and aircraft.engines=2
group by month order by month asc;
I then have two questions:
1. How do I (or even *can* I) integrate the second query into the
first as a subselect? Each individual flight can only be made in one
aircraft (I have to land to get off!), so hrs_total is all I need from
flights, and I just have to check whether that aircraft's callsign is
listed in the aircraft table as having engines=2.
2. Not all months have multi-engine time... will these months show up
in the subselect with a zero or give an error?
3. I still find the subselect syntax confusing; I will continue to
read the docs to get my head around it, though.
Thanks for any pointers... I'll keep playing on this end.
Cheers,
--
Rodolfo J. Paiz <rpaiz@simpaticus.com>