How to use rollup/cube in pivot table (SUMMARIZE ROWS)

Question

So here is my sample data:

db<>fiddle

and here is my query:

select * from (
select
    typ,
    user_name,
    sort,
    count_pc,
    weight    

from stat where typ=1 and dat>=trunc(sysdate)

)
pivot
(SUM(to_number(count_pc)) as pc, SUM(to_number(round(weight,0))) as hm
for sort in ('Alcohol','Food','NotFood' ,'Cigarette' ) )    

order by user_name asc

TYP	USER_NAME	'Alcohol'_PC	'Alcohol'_HM	'Food'_PC	'Food'_HM	'NotFood'_PC	'NotFood'_HM	'Cigarette'_PC	'Cigarette'_HM
1	XX	24	630	24	630	null	null	null	null
1	XY	64	1130	null	null	null	null	38	1130
1	XZ	null	null	null	null	128	5130	null	null

But I want a sum_pc, sum_weight at and of the table (one more row- in this case 2). Which user total how many pc and weight has....

DESIRED OUTPUT -- with yellow:

Answer 1

I think you don't need to use any rollup function. I am quite sure that perhaps there is a better and more elegant solution, but to obtain the output you want you might use this

Tip: I had to use a subselect in order to get rid off the column names coming out of the pivot.

select 
b.* , 
coalesce(alcohol_pc,0)+coalesce(food_pc,0)+coalesce(notfood_pc,0)+coalesce(cigarette_pc,0) as sum_pc,
coalesce(alcohol_hm,0)+coalesce(food_hm,0)+coalesce(notfood_hm,0)+coalesce(cigarette_hm,0) as sum_hm
from 
(
select typ, 
       user_name,
       "'Alcohol'_PC" as alcohol_pc , 
       "'Alcohol'_HM" as alcohol_hm ,
       "'Food'_PC"    as food_pc ,
       "'Food'_HM"    AS food_hm , 
       "'NotFood'_PC" as notfood_pc ,
       "'NotFood'_HM" as notfood_hm ,
       "'Cigarette'_PC" as cigarette_pc ,
       "'Cigarette'_HM" as cigarette_hm 
from 
(
select
    typ,
    user_name,
    sort,
    count_pc,
    weight
from stat where typ=1 and dat>=trunc(sysdate)
)
pivot
(SUM(to_number(count_pc)) as pc, SUM(to_number(round(weight,0))) as hm
for sort in ('Alcohol','Food','NotFood' ,'Cigarette' ) )
order by user_name asc
) b

db<>fiddle