GROUP BY with weighted average

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?

Asked by user100880 (1,387 )
Oct 24, 2017 at 21:42