Using ratio_to_report with 3 or more groupings Oracle SQL

Question

I am attempting to setup a query to aggregate the % total of sales based on Store number, Customer segment, and Brand groupings. Ideally I want to have the results as following:

 Store --------Segment-------Brand------Sales--------pct_total
    1              A            X         50            66.6
    1              A            Y         25            33.3
    1              B            X         25            25.0
    1              B            Y         25            25.0
    1              B            Z         50            50.0

This is the code I have so far from a table that contains the data necessary for this.

 select Store,Segment,Brand, to_char(100 * ratio_to_report(total_sales) over (partition by store, segment, brand),'990.00L','NLS_CURRENCY=%') as pct_total
 from ( 
         select 
           Store,
           Segment,
           Brand,
           sum(sales) as total_sales
           from customer_data 
           group by grouping sets ((Store),(store,Segment,brand)))

It just gives me an error due to the grouping

Answer 1

There's no need for Grouping Sets, this will return the expected result:

select Store,Segment,Brand, total_sales,
   to_char(100 * ratio_to_report(total_sales) over (partition by store, segment),'990.00L','NLS_CURRENCY=%') as pct_total
from ( 
        select 
          Store,
          Segment,
          Brand,
          sum(sales) as total_sales
          from customer_data 
          group by store,Segment,brand)