Oracle SQL - Generate aggregate rows for certain rows using select

Question

I have a table like below.

|FILE| ID |PARENTID|SHOWCHILD|CAT1|CAT2|CAT3|TOTAL|
|F1  | A1 |  P1    |     N   | 3  | 2  | 6  | 11  |
|F2  | A2 |  P2    |     N   | 4  | 7  | 3  | 14  |
|F3  | A3 |  P1    |     N   | 3  | 1  | 1  | 5   |
|F4  | LG1|        |     Y   | 6  | 3  | 7  | 16  |
|F5  | LG2|        |     Y   | 4  | 7  | 3  | 14  |

Now, Is it possible if I want to find the total (ie) aggregate of cat1, cat2, cat3 & total only for rows which has showChild as 'Y' and add that to the resultset.

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

Expected final output:

|FILE| ID |PARENTID|SHOWCHILD|CAT1|CAT2|CAT3|TOTAL|
|F1  | A1 |  P1    |     N   | 3  | 2  | 6  | 11  |
|F2  | A2 |  P2    |     N   | 4  | 7  | 3  | 14  |
|F3  | A3 |  P1    |     N   | 3  | 1  | 1  | 5   |
|F4  | LG1|        |     Y   | 6  | 3  | 7  | 16  |
|F5  | LG2|        |     Y   | 4  | 7  | 3  | 14  |
|Tot | Res|  Res   |     N   | 10 | 10 | 10 | 30  |

Here I have added the Tot row(last row) after considering only the rows which has showchild as 'Y' and added that to the resultset.

I am trying for a solution without using UNION

Any help on achieving the above results is highly appreciated.

Thank you.

Answer 1

One approach would be to use a union:

WITH cte AS (
    SELECT "FILE", ID, PARENTID, SHOWCHILD, CAT1, CAT2, CAT3, TOTAL, 1 AS position
    FROM yourTable
    UNION ALL
    SELECT 'Tot', 'Res', 'Res', 'N', SUM(CAT1), SUM(CAT2), SUM(CAT3), SUM(TOTAL), 2
    FROM yourTable
    WHERE SHOWCHILD = 'Y'
)

SELECT "FILE", ID, PARENTID, SHOWCHILD, CAT1, CAT2, CAT3, TOTAL
FROM cte
ORDER BY
    position,
    "FILE";

Demo