0

I have a road_insp table:

create table road_insp
(
    insp_id int,
    road_id int,
    insp_date date,
    condition number(10,2),
    insp_length number(10,2)
);

insert into road_insp (insp_id, road_id, insp_date, condition, insp_length) values ( 1, 100, to_date('2017-01-01 6:00:00AM','YYYY-MM-DD HH:MI:SSAM'), 5.0, 100);
insert into road_insp (insp_id, road_id, insp_date, condition, insp_length) values ( 2, 101, to_date('2017-02-01 7:00:00AM','YYYY-MM-DD HH:MI:SSAM'), 5.5, 500);
insert into road_insp (insp_id, road_id, insp_date, condition, insp_length) values ( 3, 101, to_date('2017-02-01 8:00:00AM','YYYY-MM-DD HH:MI:SSAM'), 6.0, 1000);
insert into road_insp (insp_id, road_id, insp_date, condition, insp_length) values ( 4, 102, to_date('2018-04-01 9:00:00AM','YYYY-MM-DD HH:MI:SSAM'), 6.5, 1500);
insert into road_insp (insp_id, road_id, insp_date, condition, insp_length) values ( 5, 102, to_date('2018-04-01 10:00:00AM','YYYY-MM-DD HH:MI:SSAM'), 7.0, 2000);
insert into road_insp (insp_id, road_id, insp_date, condition, insp_length) values ( 6, 102, to_date('2018-04-01 11:00:00AM','YYYY-MM-DD HH:MI:SSAM'), 7.5, 2500);
insert into road_insp (insp_id, road_id, insp_date, condition, insp_length) values ( 7, 103, to_date('2018-07-01 12:00:00PM','YYYY-MM-DD HH:MI:SSAM'), 8.0, 3000);
insert into road_insp (insp_id, road_id, insp_date, condition, insp_length) values ( 8, 103, to_date('2018-07-01 1:00:00PM','YYYY-MM-DD HH:MI:SSAM'), 8.5, 3500);
insert into road_insp (insp_id, road_id, insp_date, condition, insp_length) values ( 9, 103, to_date('2019-09-01 2:00:00PM','YYYY-MM-DD HH:MI:SSAM'), 9.0, 4000);
insert into road_insp (insp_id, road_id, insp_date, condition, insp_length) values ( 10, 103, to_date('2019-09-01 3:00:00PM','YYYY-MM-DD HH:MI:SSAM'), 9.5, 4500);

commit;

select
    insp_id,
    road_id,
    to_char(insp_date, 'YYYY-MM-DD HH:MI:SSAM') date_formatted,
    condition,
    insp_length
from 
    road_insp;

INSP_ID    ROAD_ID    DATE_FORMATTED         CONDITION INSP_LENGTH
---------- ---------- --------------------- ---------- -----------
         1        100 2017-01-01 06:00:00AM          5         100
         2        101 2017-02-01 07:00:00AM        5.5         500
         3        101 2017-02-01 08:00:00AM          6        1000
         4        102 2018-04-01 09:00:00AM        6.5        1500
         5        102 2018-04-01 10:00:00AM          7        2000
         6        102 2018-04-01 11:00:00AM        7.5        2500
         7        103 2018-07-01 12:00:00PM          8        3000
         8        103 2018-07-01 01:00:00PM        8.5        3500
         9        103 2019-09-01 02:00:00PM          9        4000
        10        103 2019-09-01 03:00:00PM        9.5        4500

I can successfully summarize the table by grouping inspections that have the same road_id, that were inspected on the same day.

select
    road_id,
    to_char(trunc( insp_date), 'YYYY-MM-DD') as insp_day,
    avg(condition) condition_avg,
    sum(insp_length) insp_length_tot
from
    road_insp
group by
    road_id,
    trunc(insp_date)
order by
    road_id,
    trunc(insp_date)

   ROAD_ID INSP_DAY   CONDITION_AVG INSP_LENGTH_TOT
---------- ---------- ------------- ---------------
       100 2017-01-01             5             100
       101 2017-02-01          5.75            1500
       102 2018-04-01             7            6000
       103 2018-07-01          8.25            6500   --Same road, different day
       103 2019-09-01          9.25            8500   --Same road, different day

Rather than summarize condition as an average, I would like to summarize condition as a weighted average (weighted by insp_length).

It would look like this:

+---------+------------+------------------------+-----------------+
| ROAD_ID |  INSP_DAY  | CONDITION_WEIGHTED_AVG | INSP_LENGTH_TOT |
+---------+------------+------------------------+-----------------+
|     100 | 2017-01-01 |                   5.00 |             100 |
|     101 | 2017-02-01 |                   5.83 |            1500 |
|     102 | 2018-04-01 |                   7.08 |            6000 |
|     103 | 2018-07-01 |                   8.27 |            6500 |
|     103 | 2019-09-01 |                   9.26 |            8500 |
+---------+------------+------------------------+-----------------+

How can I do this?

1
select
    road_id,
    to_char(trunc( insp_date), 'YYYY-MM-DD') as insp_day,
    avg(condition) condition_avg,
    sum(insp_length) insp_length_tot,
    round(sum(condition * insp_length) / sum(insp_length), 2) as CONDITION_WEIGHTED_AVG 
from
    road_insp
group by
    road_id,
    trunc(insp_date)
order by
    road_id,
    trunc( insp_date);

   ROAD_ID INSP_DAY   CONDITION_AVG INSP_LENGTH_TOT CONDITION_WEIGHTED_AVG
---------- ---------- ------------- --------------- ----------------------
       100 2017-01-01             5             100                      5
       101 2017-02-01          5.75            1500                   5.83
       102 2018-04-01             7            6000                   7.08
       103 2018-07-01          8.25            6500                   8.27
       103 2019-09-01          9.25            8500                   9.26

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.