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?