How can I add add subtotals to Oracle Query which uses ratio_to_report?

Question

I am trying to add totals to a moderately simple query which contains RATIO_TO_REPORT. I reviewed the Stack solution (among others) and I was unable to make it work in with my query.

My query is;

select category, sum(selected_widgets) sum_selected_widgets, to_char(round(RATIO_TO_REPORT(sum(selected_widgets)) over (),4)*100,'999d99') || '%' percentage_selected, 
       count(*) widget_count, to_char(round(RATIO_TO_REPORT(count(*)) over (),4)*100,'999d99') || '%' percentage_all_widgets
from
(
  select widget_id, widget_seq, decode(widget_category,'1100',1,'2372',1,'2650',1,'3932',1,0) selected_widgets, min(category) category
  from widget
  inner join widget_detail wd on wd.widget_id = widget_id and wd.widget_seq = widget_seq and category is not null
  where widget_dx_date between '2018    ' and '20219999'
  group by widget_id, widget_seq, decode(widget_category,'1100',1,'2372',1,'2650',1,'3932',1,0)
)iq
group by category
order by 1;

All I am trying to do is get totals for the last 4 columns. If I add "rollup" to the group by, it uses the rollup row as part of the percentage calculations, as shown here;

0   6886       3.20%    77871      5.32%
1   40689     18.91%    256538    17.54%
2   8898       4.13%    43615      2.98%
3   9817       4.56%    52809      3.61%
4   5087       2.36%    24683      1.69%
7   27397     12.73%    169669    11.60%
8   4451       2.07%    24148      1.65%
9   4371       2.03%    82084      5.61%
    107596    50.00%    731417    50.00%

How can I add totals to this query?

Answer 1

I found another Stack Answer which did not help until I clicked the DB Fiddle link and analyzed the code.

What I was missing was that yes I needed rollup, but I also needed;

1. The case statement in the ratio_to_report clause.
2. The coalesce, to handle the null.

My best explanation, please correct my errors, I will update this answer.

The case statement tells the ratio_to_report to only count grouping_id category and ignore the total line.