Not quite managing my first join
От | Rodolfo J. Paiz |
---|---|
Тема | Not quite managing my first join |
Дата | |
Msg-id | 1113079753.8752.25.camel@rodolfo.casa.paiz.org обсуждение исходный текст |
Ответы |
Re: Not quite managing my first join
|
Список | pgsql-novice |
Hi! I'm within a hair of getting my first join to work, but something is wrong and for the life of me I can't figure out why. Hints, anyone? ==================================================================== select t1.month,t1.single,t2.multi from ( select to_char(date, 'YYYY-MM') as month, sum(hrs_total) as single from flights,aircraft where flights.callsign=aircraft.callsign and aircraft.engines=1 group by month order by month) as t1 full outer join ( select to_char(date, 'YYYY-MM') as month, sum(hrs_total) as multi from flights,aircraft where flights.callsign=aircraft.callsign and aircraft.engines=2 group by month order by month) as t2 on (t1.month = t2.month); month | single | multi ---------+--------+------- 2003-08 | 5.6 | 2003-09 | 3.5 | 2003-10 | 4.2 | 2003-11 | 17.7 | 2003-12 | 16.4 | 2004-01 | 13.3 | 2004-02 | 1.7 | 7.6 2004-03 | 12.2 | 2004-04 | 13.3 | 4.1 2004-05 | 7.3 | 2004-06 | 7.1 | 9.4 2004-07 | 2.8 | 7.6 2004-08 | 6.0 | 7.0 2004-09 | 7.9 | 2.4 2004-10 | 2.5 | | | 17.4 2004-12 | 5.8 | 3.1 | | 10.1 | | 14.1 2005-03 | 9.5 | 2.7 (20 rows) ==================================================================== The problem is that the query seems to be returning all the correct results, *BUT* I have two problems: - Instead of zero where I did not fly a multi-engine airplane, it shows no value at all. - For columns where t1 had no value, no month is shown. How can I fix those two little details? My brain is temporarily fried and I'm not understanding the docs very clearly. Thanks! -- Rodolfo J. Paiz <rpaiz@simpaticus.com>
В списке pgsql-novice по дате отправления: