Summary statistics: Multiple joins and group by I have a sidewalk inspection system:

SIDEWALK_INSP

   INSP_ID
----------
         1
         2
         3

SIDEWALK_DEFICIENCY

    DEF_ID      INSP_ID   DEF_LENGTH
----------   ----------   ----------
        10            1           .5
        11            1            1
        12            1          1.5
        13            2            2
        14            2          2.5

SIDEWALK_OBSERVATION    

    OBS_ID      INSP_ID   OBS_LENGTH   OBS_TYPE
----------   ----------   ----------   --------
       100            2            5      a    
       101            2            6      b    
       102            2            7      b    
       103            2            8      b    
       104            3            9      a    
       105            3           10      a    
       106            3           11      b    




create table sidewalk_insp (insp_id number(10));
insert into sidewalk_insp values (1);
insert into sidewalk_insp values (2);
insert into sidewalk_insp values (3);
commit;

create table sidewalk_deficiency (
    def_id number(10), 
    insp_id number(10),
    def_length number(10,2));
insert into sidewalk_deficiency values (10,1,.5);
insert into sidewalk_deficiency values (11,1,1);
insert into sidewalk_deficiency values (12,1,1.5);
insert into sidewalk_deficiency values (13,2,2);
insert into sidewalk_deficiency values (14,2,2.5);
commit;

create table sidewalk_observation (
    obs_id number(10), 
    insp_id number(10),
    obs_length number(10,2),
    obs_type varchar(5));
insert into sidewalk_observation values (100,2,5,'a');
insert into sidewalk_observation values (101,2,6,'b');
insert into sidewalk_observation values (102,2,7,'b');
insert into sidewalk_observation values (103,2,8,'b');
insert into sidewalk_observation values (104,3,9,'a');
insert into sidewalk_observation values (105,3,10,'a');
insert into sidewalk_observation values (106,3,11,'b');
commit;




I want to calculate the following in a single query:


Length of deficiencies (per inspection)
Length of observations, where observation type = 'a' (per inspection)
Length of observations, regardless of type (per inspection)


The output would look like this:

+---------+------------+-------------------+----------------+
| INSP_ID | DEF_LENGTH | OBS_TYPE_A_LENGTH | OBS_ALL_LENGTH |
+---------+------------+-------------------+----------------+
|       1 |          3 |                   |                |
|       2 |        4.5 |                 5 |             26 |
|       3 |            |                19 |             30 |
+---------+------------+-------------------+----------------+


How can I create this query?
Here you go:

select
  si.insp_id, sd.def_length, so.obs_type_a_length, so.obs_all_length
from
  sidewalk_insp si
  left join 
    (select insp_id, sum(def_length) as def_length from sidewalk_deficiency group by insp_id) sd
    on (si.insp_id = sd.insp_id)
  left join 
    (select insp_id, sum(case when obs_type = 'a' then obs_length else 0 end) as obs_type_a_length,
            sum(obs_length) as obs_all_length from sidewalk_observation group by insp_id) so
    on (si.insp_id = so.insp_id)
order by 
  si.insp_id
;

   INSP_ID DEF_LENGTH OBS_TYPE_A_LENGTH OBS_ALL_LENGTH
---------- ---------- ----------------- --------------
         1          3                                 
         2        4.5                 5             26
         3                           19             30