[ncalkins]

Hello all,

I am trying to write a single SELECT statement that groups at 2 levels of aggregation (using grouping sets) and assigns row numbers (to rank each item) that are partitioned at the correct level for each grouping set. I have the grouping sets figured out but I can't find a way to make Partition By match each level of aggregation.

What I am looking for (in a single SELECT statement) is logically equivalent to:

SELECT 
week
,region
,NULL as country
,item
,SUM(qty)
,ROW_NUMBER OVER (PARTITION BY week, region ORDER BY SUM(qty) DESC) as rownum
FROM base
GROUP BY 
week
,region
,item
UNION ALL

SELECT 
week
,NULL as region
,country
,item
,SUM(qty)
,ROW_NUMBER OVER (PARTITION BY week, country ORDER BY SUM(qty) DESC) as rownum
FROM base
GROUP BY 
week
,country
,item

I hoped that I could do something like this:

SELECT 
week
,region
,country
,item
,SUM(qty)
,ROW_NUMBER OVER (PARTITION BY week, GROUPING SETS (region, country) ORDER BY SUM(qty) DESC) as rownum
FROM base
GROUP BY 
week
,GROUPING SETS (region, country)
,item

But it looks like I am not allowed to partition by grouping sets -- I get the error ORA-00907: missing right parenthesis. I didn't expect it to work but I am not sure how else to partition by multiple levels.

Any help would be greatly appreciated! Please let me know if I could have tagged my code or met other forum standards better.

[spacebar208]

What do want the output to look like?