1

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?

2

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
  • In your case statement, you specify else 0. Out of curiosity, why is this necessary? Is there a reason why you don't just let it be null, or is this just personal preference? – Wilson Feb 7 '18 at 19:35
  • 1
    @Wilson Personal preference. I don't trust the database. – Balazs Papp Feb 7 '18 at 19:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.