Hello gurus,
I have data like this .
WITH p_data
AS (SELECT 'Americas' AS region\_name,
'Canada' AS countr\_name,
103456 AS instance\_id,
1 AS order\_cnt
FROM dual
UNION ALL
SELECT 'Americas' AS region\_name,
'Canada' AS countr\_name,
103457,
1 AS order\_cnt
FROM dual
UNION ALL
SELECT 'Americas' AS region\_name,
'United States of America' AS countr\_name,
103458,
1 AS order\_cnt
FROM dual
UNION ALL
SELECT 'Americas' AS region\_name,
'United States of America' AS countr\_name,
103459,
1 AS order\_cnt
FROM dual
UNION ALL
SELECT 'Europe' AS region\_name,
'Germany' AS countr\_name,
103459,
1 AS order\_cnt
FROM dual
UNION ALL
SELECT 'Europe' AS region\_name,
'Germany' AS countr\_name,
103460,
1 AS order\_cnt
FROM dual
UNION ALL
SELECT 'Europe' AS region\_name,
'Germany' AS countr\_name,
103460,
1 AS order\_cnt
FROM dual
UNION ALL
SELECT 'Europe' AS region\_name,
'United Kingdom' AS countr\_name,
103460,
1 AS order\_cnt
FROM dual
UNION ALL
SELECT 'Europe' AS region\_name,
'United Kingdom' AS countr\_name,
103460,
1 AS order\_cnt
FROM dual),
c\_data
AS (SELECT region\_name,
Nvl(countr\_name, 'All Region') countr\_name,
SUM(Decode(order\_cnt, 1, 1,
0)) order\_count
FROM p\_data
GROUP BY region\_name,
rollup ( countr\_name ))
SELECT \*
FROM c\_data ;
I have the requirement that for Region = 'Europe' , one should see the roll up count of Europe which is sum of Germany + United Kingdom , but for America , one should see the individual
country counts and not the Roll up one .
The roll up should occur only for Europe . I know i can select using where clause but can it be achieved using Roll UP clause or Grouping Set using case condition ?
Please assist , i am trying to use case conditions in roll up but not working