Oracle SQL - Generate aggregate row using select

Question

I have a table like below.

|    FILE   | CATEGORY1 | CATEGORY2 | CATEGORY3 |
| File1.txt |    3      |     2     |     6     |
| File2.txt |    4      |     7     |     3     |
| File2.txt |    3      |     1     |     1     |

Now, Is it possible to add a new row as part of select query which will provide the below row added to the resultset.

| Total     |    10     |     10    |     10    |

Expected final output:

|    FILE   | CATEGORY1 | CATEGORY2 | CATEGORY3 |
| File1.txt |    3      |     2     |     6     |
| File2.txt |    4      |     7     |     3     |
| File2.txt |    3      |     1     |     1     |
| Total     |    10     |     10    |     10    |

Any help on achieving the above results is highly appreciated.

Thank you.

Gauravsa · Accepted Answer · 2019-02-12 04:21:59Z

3

You can use this:

SELECT FILE, CATEGORY1, CATEGORY2, CATEGORY3 From TestTable
union all
SELECT 'Total', Sum([CATEGORY1]), Sum([CATEGORY2]), Sum([CATEGORY3]) From TestTable

If values in your column allow nulls, then can use function like nvl.

You can also try rollup also:

SELECT File, Sum([CATEGORY1]), Sum([CATEGORY2]), Sum([CATEGORY3]) From
TestTable
group by rollup (File);

edited Feb 12, 2019 at 4:21

answered Feb 12, 2019 at 4:15

Gauravsa

6,6382 gold badges25 silver badges33 bronze badges

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

1 Comment

Alphonsa Over a year ago

Thank you for your response. I have another query similar to the above one. stackoverflow.com/questions/54644206/… Would be helpful if you can consider checking that as well. Thank you.

Collectives™ on Stack Overflow

Oracle SQL - Generate aggregate row using select

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related