Hi Gurus,
I have a schema as described in the link
https://livesql.oracle.com/ords/livesql/s/ct0zdah8slxq66h8fnpfhwdby drop table t;
create table t
(
instance number ,
id_sup number ,
id number ,
srt number ,
v01 number ,
v02 number ,
v03 number ,
value_01 number ,
value_02 number
)
;
insert into t values (200,1,2,2,26,20,13,20,13);
insert into t values (200,1,3,3,30,23,15,23,23);
insert into t values (200,1,4,4,18,13,9,13,13);
insert into t values (200,1,5,5,22,16,11,16,16);
insert into t values (200,1,75,6,24,18,12,24,12);
insert into t values (200,74,76,8,26,20,13,20,26);
insert into t values (200,74,77,9,28,21,14,28,21);
insert into t values (200,74,10,10,28,21,14,14,21);
insert into t values (200,74,79,11,34,26,17,26,17);
insert into t values (200,74,80,12,22,16,11,16,16);
insert into t values (200,74,81,13,17,13,8,13,17);
insert into t values (200,74,82,14,27,20,14,20,27);
insert into t values (200,74,83,15,22,16,11,11,11);
insert into t values (200,74,84,16,24,18,12,18,24);
insert into t values (200,85,20,18,18,13,9,9,13);
insert into t values (200,85,21,19,34,26,17,17,34);
insert into t values (200,85,22,20,21,16,11,11,11);
insert into t values (200,85,86,21,19,14,9,9,9);
insert into t values (200,23,30,23,29,21,15,15,21);
insert into t values (200,23,24,24,30,23,15,15,30);
insert into t values (200,23,90,25,30,23,15,23,23);
insert into t values (200,23,26,26,30,23,15,30,30);
insert into t values (200,23,87,27,30,23,15,23,23);
insert into t values (200,23,88,28,24,17,12,17,24);
insert into t values (200,29,89,30,16,12,8,8,8);
insert into t values (200,29,94,31,40,30,20,30,40);
insert into t values (200,29,91,32,21,16,11,21,16);
insert into t values (200,29,92,33,36,27,18,27,18);
insert into t values (200,29,95,34,38,28,18,28,38);
insert into t values (200,29,93,35,36,27,18,18,18);
select *
from t
order by srt
;
I want to have sub-totals like this way :
INSTANCE ID_SUP ID SRT V01 V02 V03 VALUE_01 VALUE_02
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
200 1 2 2 26 20 13 20 13
200 1 3 3 30 23 15 23 23
200 1 4 4 18 13 9 13 13
200 1 5 5 22 16 11 16 16
200 1 75 6 24 18 12 24 12
200 TOTAL 120 96 77
200 74 76 8 26 20 13 20 26
200 74 77 9 28 21 14 28 21
200 74 10 10 28 21 14 14 21
200 74 79 11 34 26 17 26 17
200 74 80 12 22 16 11 16 16
200 74 81 13 17 13 8 13 17
200 74 82 14 27 20 14 20 27
200 74 83 15 22 16 11 11 11
200 74 84 16 24 18 12 18 24
200 TOTAL 228 166 180
200 85 20 18 18 13 9 9 13
200 85 21 19 34 26 17 17 34
200 85 22 20 21 16 11 11 11
200 85 86 21 19 14 9 9 9
200 TOTAL 120 46 67
200 23 30 23 29 21 15 15 21
200 23 24 24 30 23 15 15 30
200 23 90 25 30 23 15 23 23
200 23 26 26 30 23 15 30 30
200 23 87 27 30 23 15 23 23
200 23 88 28 24 17 12 17 24
200 TOTAL 173 123 151
200 29 89 30 16 12 8 8 8
200 29 94 31 40 30 20 30 40
200 29 91 32 21 16 11 21 16
200 29 92 33 36 27 18 27 18
200 29 95 34 38 28 18 28 38
200 29 93 35 36 27 18 18 18
200 TOTAL 187 132 138
200 TOTAL 708 563 613 613So I want the sub-total and the grand-total for v01 and for values : value_01 and value_02.
Thanks in advance
Use grouping sets to get specific subtotals, like this:
group by grouping sets (
( instance, id_sup, srt, v01, v02, v03, value_01, value_02 ),
( instance, id_sup ),
( instance )
)
This gets the raw values and them summed up by instance & id_sup. To only show the subtotals for a few columns, use the grouping function to see which subtotal you're at. Use this in a case expression to return null for the columns you don't want the subtotals for.
e.g.
case when grouping ( v01 ) = 0 then sum ( v02 ) end v02
It looks like you want the data ordered by SRT, respecting the subtotals. So return the MAX ( SRT ) to order by this.
Which gives something along the lines of:
select instance, id_sup,
grouping ( v01 ) v01_grp,
grouping ( id_sup ) id_sup_grp,
case when grouping ( v01 ) = 0 then sum ( id ) end id,
case when grouping ( v01 ) = 0 then sum ( srt ) end srt,
sum ( v01 ) v01,
case when grouping ( v01 ) = 0 then sum ( v02 ) end v02,
case when grouping ( v01 ) = 0 then sum ( v03 ) end v03,
sum ( value_01 ) value_01,
sum ( value_02 ) value_02,
max ( srt ) mx_srt
from t
group by grouping sets (
( instance, id_sup, srt, v01, v02, v03, value_01, value_02 ),
( instance, id_sup ),
( instance )
)
order by mx_srt, v01_grp, id_sup_grp;
INSTANCE ID_SUP V01_GRP ID_SUP_GRP ID SRT V01 V02 V03 VALUE_01 VALUE_02 MX_SRT
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
200 1 0 0 2 2 26 20 13 20 13 2
200 1 0 0 3 3 30 23 15 23 23 3
200 1 0 0 4 4 18 13 9 13 13 4
200 1 0 0 5 5 22 16 11 16 16 5
200 1 0 0 75 6 24 18 12 24 12 6
200 1 1 0 <null> <null> 120 <null> <null> 96 77 6
200 74 0 0 76 8 26 20 13 20 26 8
200 74 0 0 77 9 28 21 14 28 21 9
200 74 0 0 10 10 28 21 14 14 21 10
200 74 0 0 79 11 34 26 17 26 17 11
200 74 0 0 80 12 22 16 11 16 16 12
200 74 0 0 81 13 17 13 8 13 17 13
200 74 0 0 82 14 27 20 14 20 27 14
200 74 0 0 83 15 22 16 11 11 11 15
200 74 0 0 84 16 24 18 12 18 24 16
200 74 1 0 <null> <null> 228 <null> <null> 166 180 16
200 85 0 0 20 18 18 13 9 9 13 18
200 85 0 0 21 19 34 26 17 17 34 19
200 85 0 0 22 20 21 16 11 11 11 20
200 85 0 0 86 21 19 14 9 9 9 21
200 85 1 0 <null> <null> 92 <null> <null> 46 67 21
200 23 0 0 30 23 29 21 15 15 21 23
200 23 0 0 24 24 30 23 15 15 30 24
200 23 0 0 90 25 30 23 15 23 23 25
200 23 0 0 26 26 30 23 15 30 30 26
200 23 0 0 87 27 30 23 15 23 23 27
200 23 0 0 88 28 24 17 12 17 24 28
200 23 1 0 <null> <null> 173 <null> <null> 123 151 28
200 29 0 0 89 30 16 12 8 8 8 30
200 29 0 0 94 31 40 30 20 30 40 31
200 29 0 0 91 32 21 16 11 21 16 32
200 29 0 0 92 33 36 27 18 27 18 33
200 29 0 0 95 34 38 28 18 28 38 34
200 29 0 0 93 35 36 27 18 18 18 35
200 29 1 0 <null> <null> 187 <null> <null> 132 138 35
200 <null> 1 1 <null> <null> 800 <null> <null> 563 613 3