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?
Best Answer