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.

Tim Biegeleisen · Accepted Answer · 2019-02-12 07:00:26Z

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

edited Feb 12, 2019 at 7:00

answered Feb 12, 2019 at 6:46

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alphonsa Over a year ago

Thank you for your response. For simplicity, I had given like taking from a single table whereas in my scenario, I have the table content in a large subquery. So if I use union it will be like using the subquery two times to generate the result.

Tim Biegeleisen Over a year ago

Then you asked the wrong question. I have answered the actual question you asked.

Fahmi · Accepted Answer · 2019-02-12 06:58:23Z

1

You can try using UNION

select FILE,ID ,PARENTID,SHOWCHILD,CAT1,CAT2,CAT3,TOTAL from table1
union 
select 'Tot','Res','Res','N',sum(cat1), sum(cat2),sum(cat3), sum(total)
from table1 where SHOWCHILD='Y'

edited Feb 12, 2019 at 6:58

answered Feb 12, 2019 at 6:47

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

2 Comments

Tim Biegeleisen Over a year ago

This won't generate the output which the OP is expecting though.

Alphonsa Over a year ago

I am trying for a solution without UNION

Matthew McPeak · Accepted Answer · 2019-02-13 01:58:10Z

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 |
+------+-----+-----+----------+-----------+------+------+------+-------+

Collectives™ on Stack Overflow

Oracle SQL - Generate aggregate rows for certain rows using select

3 Answers 3

Demo

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related