Hello experts;
I have the following sample data below and the code i am working on as well
with t (namereq, amt, group_name) as
(
select 'A1', 2, 'A' from dual
union all
select 'A2', 3, 'A' from dual
union all
select 'A3', 4, 'A' from dual
union all
select 'B1', 3, 'B' from dual
union all
select 'B2', 5, 'B' from dual
)
desired output below
Namereq Amt Group_name
A1 2 A
A2 3 A
A3 4 A
Subtotal 9 A
B1 3 B
B2 5 B
Subtotal 8 B
Total 17 null
Code created so far
with t (namereq, amt, group_name) as
(
select 'A1', 2, 'A' from dual
union all
select 'A2', 3, 'A' from dual
union all
select 'A3', 4, 'A' from dual
union all
select 'B1', 3, 'B' from dual
union all
select 'B2', 5, 'B' from dual
)
select namereq
,sum(amt) as amt
,group_name
from t
group by rollup (group_name, namereq)
order by group_name
However, I decided to adapt the code to use grouping sets to get the desired output but i am getting some strange error
with t (namereq, amt, group_name) as
(
select 'A1', 2, 'A' from dual
union all
select 'A2', 3, 'A' from dual
union all
select 'A3', 4, 'A' from dual
union all
select 'B1', 3, 'B' from dual
union all
select 'B2', 5, 'B' from dual
)
select namereq
,group_name
,sum(amt)
,GROUPING_ID(group_name, namereq) AS grouping_id
,GROUP_ID() AS group_id
from t
group by grouping sets(namereq, CUBE (group_name, namereq))
ORDER BY namereq, group_name;
error below
ORA-00904: "SYS_TBL_$1$"."NAMEREQ": invalid identifier
Also, which is better to use roll-up or cube or grouping-sets in the above scenario