Select attributes from parent rows (and include cost of children)

Question

I have a WORKORDER table that has parent and child WOs in it. There are only two levels to the hierarchy: parent and children (no grandchildren, etc.)

---

with workorder as (
select 'WO37342' as wonum,      null as parent, 'WINTER STORM' as classification, 297.36 as actlabcost, 200 as actmatcost from dual
union all
select 'WO37427' as wonum, 'WO37342' as parent, 'ANTI-ICING'   as classification,  99.12 as actlabcost,   0 as actmatcost from dual
union all
select 'WO37429' as wonum, 'WO37342' as parent, 'SNOW FENCE'   as classification,  99.12 as actlabcost, 100 as actmatcost from dual
)
select
    * 
from
    workorder


WONUM     PARENT    CLASSIFICATION ACTLABCOST ACTMATCOST
-------   -------   -------------- ---------- ----------
WO37342             WINTER STORM       297.36        200
WO37427   WO37342   ANTI-ICING          99.12          0
WO37429   WO37342   SNOW FENCE          99.12        100

---

I want to select the attributes from the parent rows and include the cost of the children:

WONUM     CLASSIFICATION ACTLABCOST ACTMATCOST
-------   -------------- ---------- ----------
WO37342   WINTER STORM        495.6        300

---

Is there a concise way of doing this in Oracle 18c?

(My goal is for the SQL to be as simple/readable as possible.)

Answer 1

You can use aggregation:

select coalesce(parent, wonum) as wonum,
       max(case when parent is null then classification end) as classification,
       sum(ACTLABCOST) as ACTLABCOST,
       sum(ACTMATCOST) as ACTMATCOST
from t
group by coalesce(parent, wonum);

This seems simple enough to me.