using(select+)+oracle,Oracle SQL - Generate aggregate rows for certain rows using select

I see you already accepted an answer, but you did ask for a solution that did not involve UNION. One such solution would be to use GROUPING SETS.

GROUPING SETS allow you to specify different grouping levels in your query and generate aggregates at each of those levels. You can use it to generate an output row for each record plus a single "total" row, as per your requirements. The function GROUPING can be used in expressions to identify whether each output row is in one group or the other.

Example, with test data:

with input_data ("FILE", "ID", PARENTID, SHOWCHILD, CAT1, CAT2, CAT3, TOTAL ) AS (

SELECT 'F1','A1','P1','N',3,2,6,11 FROM DUAL UNION ALL

SELECT 'F2','A2','P2','N',4,7,3,14 FROM DUAL UNION ALL

SELECT 'F3','A3','P1','N',3,1,1,5 FROM DUAL UNION ALL

SELECT 'F4','LG1','','Y',6,3,7,16 FROM DUAL UNION ALL

SELECT 'F5','LG2','','Y',4,7,3,14 FROM DUAL )

SELECT decode(grouping("FILE"),1,'Tot',"FILE") "FILE",

decode(grouping("ID"),1,'Res',"ID") "ID",

decode(grouping(parentid),1, 'Res',parentid) parentid,

decode(grouping(showchild),1, 'N',showchild) showchild,

decode(grouping("FILE"),1,sum(decode(showchild,'Y',cat1,0)),sum(cat1)) cat1,

decode(grouping("FILE"),1,sum(decode(showchild,'Y',cat2,0)),sum(cat2)) cat2,

decode(grouping("FILE"),1,sum(decode(showchild,'Y',cat3,0)),sum(cat3)) cat3,

decode(grouping("FILE"),1,sum(decode(showchild,'Y',total,0)),sum(total)) total

from input_data

group by grouping sets (("FILE", "ID", parentid, showchild), ())

+------+-----+-----+----------+-----------+------+------+------+-------+

| FILE | F2 | ID | PARENTID | SHOWCHILD | CAT1 | CAT2 | CAT3 | TOTAL |

+------+-----+-----+----------+-----------+------+------+------+-------+

| F1 | F1 | A1 | P1 | N | 3 | 2 | 6 | 11 |

| F2 | F2 | A2 | P2 | N | 4 | 7 | 3 | 14 |

| F3 | F3 | A3 | P1 | N | 3 | 1 | 1 | 5 |

| F4 | F4 | LG1 | - | Y | 6 | 3 | 7 | 16 |

| F5 | F5 | LG2 | - | Y | 4 | 7 | 3 | 14 |

| Tot | Tot | Res | Res | N | 10 | 10 | 10 | 30 |

+------+-----+-----+----------+-----------+------+------+------+-------+