About sub-totals in Oracle SQL

Question

I have a problem to get sub-totals for a specific query. Here is the schema I am working on :

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 a sub-total and the grand-total for v01 only. I want also to calculate the sub-totals and grand-totals for the values value_01 and value_02.

Here is the way I want to get it

  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

So for each instance, and for each id_sup on it, I want to get the total of v01 and the totals for vlaue01 and value02

This strikes me that you want to run an aggregate on the table (sum), but the final output you want to replace the numbers with null or some other string to selectively output the results? Just wrap your first query that does the aggregate in a CTE, then select from that CTE with casts to varchar if you must have 'TOTAL" in the ID column in such a way that only the columns you want to appear in the result get an NVL value that will work if there's a rollup row generated by your aggregate. Does that make sense? — Jaaz Cole
– Jaaz Cole, Commented Jan 6 at 15:34

ArtBindu · Accepted Answer · 2025-01-06 15:55:53Z

As you want to get result tabel in format:

So, you first create table like:

SELECT DISTINCT instance, id_sup,
  sum(v01) over (partition by id_sup) as v01,
  sum(value_01) over (partition by id_sup) as value_01,
  sum(value_02) over (partition by id_sup) as value_02
FROM t

then UNION with actual table to get the actual result:

SELECT *
FROM t
union
SELECT DISTINCT instance, id_sup,
  'TOTAL' as id, '' as srt,
  sum(v01) over (partition by id_sup) as v01,
  '' as v02, '' as v03,
  sum(value_01) over (partition by id_sup) as value_01,
  sum(value_02) over (partition by id_sup) as value_02
FROM t
ORDER BY id_sup, id, v01 ASC, value_01 ASC, value_02 ASC;

Output:

If you want to show empty value at 'red' cell the use query:

select instance, 
  CASE
    WHEN(id = 'TOTAL') THEN '' 
    ELSE id_sup
  END AS id_sup,
  id, srt, v01, v02, v03,
  value_01, value_02 
from( 
  SELECT *
FROM t
union
SELECT DISTINCT instance, id_sup,
  'TOTAL' as id, '' as srt,
  sum(v01) over (partition by id_sup) as v01,
  '' as v02, '' as v03,
  sum(value_01) over (partition by id_sup) as value_01,
  sum(value_02) over (partition by id_sup) as value_02
FROM t
ORDER BY id_sup, id, v01 ASC, value_01 ASC, value_02 ASC
  ) t1;

dbfiddle

Sergey · Accepted Answer · 2025-01-06 16:02:35Z

0

if the last line in your example can be slightly different, then you can try GROUPING SETS-clause like

 select t.instance,t.id_sup,t.id,t.srt,
 sum(v01)v01,t.v02,t.v03,sum(value_01)value_01,sum(value_02)value_02
 from t t
 group by grouping sets((t.instance,t.id_sup,t.id,t.srt,t.v02,t.v03),())

answered Jan 6 at 16:02

Sergey

5,2801 gold badge9 silver badges12 bronze badges

Comments

samhita · Accepted Answer · 2025-01-06 16:05:17Z

It is not clear if you want to get Grand Total based on an key like instance or id column etc.

If it is just general sum that you want for V01 and value_01 and value_02 and other columns are replaced as empty string like @Jaaz Cole mentioned in the comment, here is an example approach.Hope my understanding is correct.

A row can be added to the result set by UNION and calculated the sum for V01, value_01 and value_02 and Total is used in ID.

WITH totals AS (
    SELECT 
        ' ' instance, 
        ' ' id_sup, 
        'TOTAL' AS id, 
        ' ' AS srt, 
        SUM(v01) AS v01, 
        ' ' AS v02, 
        ' ' AS v03, 
        SUM(value_01) AS value_01, 
        SUM(value_02) AS value_02 
    FROM 
        t
)
SELECT 
    CAST(instance AS VARCHAR2(10)) AS instance, 
    CAST(id_sup AS VARCHAR2(10)) AS id_sup, 
    CAST(id AS VARCHAR2(10)) AS id, 
    CAST(srt AS VARCHAR2(10)) AS srt, 
    v01, 
    CAST(v02 AS VARCHAR2(10)) AS v02, 
    CAST(v03 AS VARCHAR2(10)) AS v03, 
    value_01, 
    value_02 
FROM 
    t
UNION 
SELECT 
    CAST(instance AS VARCHAR2(10)) AS instance, 
    CAST(id_sup AS VARCHAR2(10)) AS id, 
    id, 
    srt, 
    v01, 
    v02, 
    v03, 
    value_01, 
    value_02 
FROM 
    totals
ORDER BY 
    instance desc;

Fiddle

Output

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	23	24	24	30	23	15	15	30
200	23	26	26	30	23	15	30	30
200	23	30	23	29	21	15	15	21
200	23	87	27	30	23	15	23	23
200	23	88	28	24	17	12	17	24
200	23	90	25	30	23	15	23	23
200	29	89	30	16	12	8	8	8
200	29	91	32	21	16	11	21	16
200	29	92	33	36	27	18	27	18
200	29	93	35	36	27	18	18	18
200	29	94	31	40	30	20	30	40
200	29	95	34	38	28	18	28	38
200	74	10	10	28	21	14	14	21
200	74	76	8	26	20	13	20	26
200	74	77	9	28	21	14	28	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	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
		TOTAL		800			563	613

d r · Accepted Answer · 2025-01-07 19:30:42Z

You could use recursive cte as a row generator to make a grid for your data.

WITH
  row_nums (RN) AS
    ( Select 1
      From   Dual
     Union All
      Select RN + 1
      From row_nums
      Where RN <= ( Select Max(SRT) From tbl ) + 1
    )

... using row numbers from above (1 to 37) - Left Join your table twice - once for SRT matching the row number (RN) and another with a shift of 1 ( SRT = RN - 1) ...
Now you can use Case expressions to get you whatever you need with a small catch regarding the ID column which is of Number datatype - here it's been converted to char since you decided to put some text in that column.
I used text "SubTotal" And "TOTAL" to make a difference in last to rows.

Select    Nvl( t.INSTANCE, t1.INSTANCE ) "INSTANCE", 
          t.ID_SUP "ID_SUP", 
          --
          Case  When r.RN - Max(t.SRT) Over() > 1 Then 'TOTAL'
                When t.ID_SUP Is Not Null Then To_Char(t.ID)
          Else 'SubTotal'
          End "ID", 
          t.SRT,
          --
          Case When r.RN - Max(t.SRT) Over() = 2 
               Then  Sum(t.V01) Over()
          Else     Nvl(t.V01, Sum(t1.V01) Over(Partition By t1.ID_SUP)) 
          End "V01", 
          --
          t.V02, t.V03, 
          --
          Case When r.RN - Max(t.SRT) Over() = 2 
               Then  Sum(t.VALUE_01) Over()
          Else     Nvl(t.VALUE_01, Sum(t1.VALUE_01) Over(Partition By t1.ID_SUP)) 
          End "VALUE_01", 
          --
          Case When r.RN - Max(t.SRT) Over() = 2 
               Then  Sum(t.VALUE_02) Over()
          Else     Nvl(t.VALUE_02, Sum(t1.VALUE_02) Over(Partition By t1.ID_SUP)) 
          End "VALUE_02"
From      row_nums r
Left Join tbl t ON( t.SRT = r.RN )
Left Join tbl t1 ON( t1.SRT = r.RN-1 )
Where     r.RN > 1 
Order By  r.RN

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	null	SubTotal	null	120	null	null	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	null	SubTotal	null	228	null	null	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	null	SubTotal	null	92	null	null	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	null	SubTotal	null	173	null	null	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	null	SubTotal	null	187	null	null	132	138
null	null	TOTAL	null	800	null	null	563	613

fiddle

Collectives™ on Stack Overflow

About sub-totals in Oracle SQL

4 Answers 4

dbfiddle

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related