Skip to Main Content
  • Questions

  • Using Analytical Functions to get Group Total

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Suresh.

Asked: October 11, 2017 - 3:34 pm UTC

Last updated: October 17, 2017 - 2:45 pm UTC

Version: 11.2.0.

Viewed 1000+ times

You Asked

I need a GROUP TOTAL for EACH MONTH that SUMS up all the values in APPL column even when the query is for a particular APPL value.

create table test_grp_total
(dttm_month date, appl varchar2(5), mins number)

insert into test_grp_total values('01-aug-2017','AAA',100);
insert into test_grp_total values('01-aug-2017','BBB',200);
insert into test_grp_total values('01-aug-2017','CCC',100);
insert into test_grp_total values('01-aug-2017','AAA',300);
insert into test_grp_total values('01-aug-2017','BBB',500);
insert into test_grp_total values('01-aug-2017','CCC',600);

insert into test_grp_total values('01-sep-2017','AAA',200);
insert into test_grp_total values('01-sep-2017','BBB',400);
insert into test_grp_total values('01-sep-2017','CCC',600);
insert into test_grp_total values('01-sep-2017','AAA',900);
insert into test_grp_total values('01-sep-2017','BBB',600);
insert into test_grp_total values('01-sep-2017','CCC',800);

select dttm_month,appl,sum(mins) from test_grp_total group by dttm_month,appl 
order by dttm_month;


select dttm_month,appl,sum(mins) from test_grp_total where appl in ( 'AAA','BBB') group by dttm_month,appl 
order by dttm_month;

--I would like to see a result like this below when querying for the column aapl for values AAA,BBB

--Which analytical function is needed for this type of result and how to use them?

DTTM_MONTH  APPL  SUM(MINS)
 8/1/2017  AAA    400
 8/1/2017  BBB    700
 GRAND TOTAL     1800  ---(THIS IS THE TOTAL FOR ALL APPL FOR THE MONTH)
 
 9/1/2017  AAA   1100
 9/1/2017  BBB   1000
 GRAND TOTAL     3500 ---(THIS IS THE TOTAL FOR ALL APPL FOR THE MONTH)




and Chris said...

It's not analytics you need to add grand total rows: it's rollup!

In your group by, rollup the appl values to get your grand totals:

create table test_grp_total
(dttm_month date, appl varchar2(5), mins number);

insert into test_grp_total values('01-aug-2017','AAA',100);
insert into test_grp_total values('01-aug-2017','BBB',200);
insert into test_grp_total values('01-aug-2017','CCC',100);
insert into test_grp_total values('01-aug-2017','AAA',300);
insert into test_grp_total values('01-aug-2017','BBB',500);
insert into test_grp_total values('01-aug-2017','CCC',600);

insert into test_grp_total values('01-sep-2017','AAA',200);
insert into test_grp_total values('01-sep-2017','BBB',400);
insert into test_grp_total values('01-sep-2017','CCC',600);
insert into test_grp_total values('01-sep-2017','AAA',900);
insert into test_grp_total values('01-sep-2017','BBB',600);
insert into test_grp_total values('01-sep-2017','CCC',800);

select dttm_month,appl,sum(mins) from test_grp_total 
group by dttm_month,rollup(appl)
order by dttm_month;

DTTM_MONTH            APPL  SUM(MINS)  
01-AUG-2017 00:00:00  AAA   400        
01-AUG-2017 00:00:00  BBB   700        
01-AUG-2017 00:00:00  CCC   700        
01-AUG-2017 00:00:00        1800       
01-SEP-2017 00:00:00  AAA   1100       
01-SEP-2017 00:00:00  BBB   1000       
01-SEP-2017 00:00:00  CCC   1400       
01-SEP-2017 00:00:00        3500

Rating

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

using grouping sets

Rajeshwaran Jeyabal, October 12, 2017 - 1:05 am UTC 

demo@ORA12C> select dttm_month,appl,sum(mins)
  2  from t
  3  where appl in ('AAA','BBB' )
  4  group by grouping sets( (dttm_month,appl),(dttm_month));

DTTM_MONTH  APPL   SUM(MINS)
----------- ----- ----------
01-AUG-2017 AAA          400
01-AUG-2017 BBB          700
01-AUG-2017             1100
01-SEP-2017 AAA         1100
01-SEP-2017 BBB         1000
01-SEP-2017             2100

6 rows selected.

demo@ORA12C>

Chris Saxon
October 12, 2017 - 11:35 am UTC

Yes, you could do that too.

Suresh Harvu, October 12, 2017 - 2:37 pm UTC

How can I order them in desc order within the grouping? So in this example, for 01-AUG-2017, the SUM(MINS) should be 700,700,400
and for 01-SEP-2017 it should be 1400,1100,1000.

TTM_MONTH APPL SUM(MINS)
01-AUG-2017 00:00:00 AAA 400
01-AUG-2017 00:00:00 BBB 700
01-AUG-2017 00:00:00 CCC 700
01-AUG-2017 00:00:00 1800
01-SEP-2017 00:00:00 AAA 1100
01-SEP-2017 00:00:00 BBB 1000
01-SEP-2017 00:00:00 CCC 1400
01-SEP-2017 00:00:00 3500
Chris Saxon
October 12, 2017 - 2:46 pm UTC

So you want to sort by date first, with date totals at the bottom of each date. And show the mins descending?

If so, the grouping_id function tells you which level in the grand total you're at. So you can include this in your order:

select dttm_month,appl,sum(mins), grouping_id(appl)
from   test_grp_total 
group by dttm_month,rollup(appl)
order by dttm_month, grouping_id(appl), sum(mins) desc;

DTTM_MONTH            APPL  SUM(MINS)  GROUPING_ID(APPL)  
01-AUG-2017 00:00:00  BBB   700        0                  
01-AUG-2017 00:00:00  CCC   700        0                  
01-AUG-2017 00:00:00  AAA   400        0                  
01-AUG-2017 00:00:00        1800       1                  
01-SEP-2017 00:00:00  CCC   1400       0                  
01-SEP-2017 00:00:00  AAA   1100       0                  
01-SEP-2017 00:00:00  BBB   1000       0                  
01-SEP-2017 00:00:00        3500       1

Suresh Harvu, October 12, 2017 - 2:42 pm UTC

Forgot to add that I would like to have the sub total be displayed as "Sub Total"

Sub Total 1800

I tried order by desc but the sub totals are going out of sync.

So the following is not working as expected
select dttm_month,appl,sum(mins) from test_grp_total
group by dttm_month,rollup(appl)
order by dttm_month,SUM(MINS) desc;

Please advise.

Chris Saxon
October 12, 2017 - 2:51 pm UTC

Again, you use the grouping_id function. If it's 1, you have a subtotal:

select dttm_month,appl,sum(mins), 
       case grouping_id(appl)
         when 1 then 'SUBTOTAL' 
         else appl
       end
from   test_grp_total 
group by dttm_month,rollup(appl)
order by dttm_month, grouping_id(appl), sum(mins) desc;

DTTM_MONTH            APPL  SUM(MINS)  CASEGROUPING_ID(APPL)WHEN1THEN'SUBTOTAL'ELSEAPPLEND  
01-AUG-2017 00:00:00  BBB   700        BBB                                                  
01-AUG-2017 00:00:00  CCC   700        CCC                                                  
01-AUG-2017 00:00:00  AAA   400        AAA                                                  
01-AUG-2017 00:00:00        1800       SUBTOTAL                                             
01-SEP-2017 00:00:00  CCC   1400       CCC                                                  
01-SEP-2017 00:00:00  AAA   1100       AAA                                                  
01-SEP-2017 00:00:00  BBB   1000       BBB                                                  
01-SEP-2017 00:00:00        3500       SUBTOTAL


You can also use this to figure out what's going on if you multiple levels of subtotals:

select dttm_month,appl,sum(mins), grouping_id(dttm_month,appl) grp,
       case grouping_id(dttm_month,appl)
         when 1 then 'SUBTOTAL' 
         when 3 then 'GRAND TOTAL'
         else appl
       end tot
from   test_grp_total 
group by rollup(dttm_month,appl)
order by dttm_month, grouping_id(dttm_month,appl), sum(mins) desc;

DTTM_MONTH            APPL  SUM(MINS)  GRP  TOT          
01-AUG-2017 00:00:00  BBB   700        0    BBB          
01-AUG-2017 00:00:00  CCC   700        0    CCC          
01-AUG-2017 00:00:00  AAA   400        0    AAA          
01-AUG-2017 00:00:00        1800       1    SUBTOTAL     
01-SEP-2017 00:00:00  CCC   1400       0    CCC          
01-SEP-2017 00:00:00  AAA   1100       0    AAA          
01-SEP-2017 00:00:00  BBB   1000       0    BBB          
01-SEP-2017 00:00:00        3500       1    SUBTOTAL     
                            5300       3    GRAND TOTAL


You can read more about this at:

https://oracle-base.com/articles/misc/rollup-cube-grouping-functions-and-grouping-sets#grouping_id

Suresh Harvu, October 12, 2017 - 3:21 pm UTC

Thank you and your team for such quick responses. I should have asked this before but realized that the query needs a bit more tweaking. Pardon me for not letting you know the whole thing in one go.

I have filtered the appl in the in list. But need the result to include all appl in the SUB TOTAL.

select dttm_month,appl,sum(mins),
case grouping_id(appl)
when 1 then 'SUBTOTAL'
else appl
end
from test_grp_total where appl in ( 'BBB','CCC')
group by dttm_month,rollup(appl)
order by dttm_month, grouping_id(appl), sum(mins) desc;

Two things that I would like to get from the query.

1. In this query above I am expecting the result to show the SUBTOTAL to be for ALL the APPL even though only two AAPL was selected in the "in" list.

2. If I just need the top n records ( based on the value of SUM(MINS)), how can that be displayed. Again this result also to show the SUBTOTAL for ALL the AAPL.

Thanks again.
Suresh

To Suresh : check if that helps

Rajeshwaran, Jeyabal, October 12, 2017 - 5:52 pm UTC

1) 
demo@ORA12C> select dttm_month,appl,sum(mins) sum_mins,
  2   case grouping_id(appl)
  3   when 1 then 'SUBTOTAL'
  4   else appl
  5   end grp
  6  from test_grp_total
  7  group by dttm_month,rollup(appl)
  8  /

DTTM_MONTH  APPL    SUM_MINS GRP
----------- ----- ---------- --------
01-AUG-2017 AAA          400 AAA
01-AUG-2017 BBB          700 BBB
01-AUG-2017 CCC          700 CCC
01-AUG-2017             1800 SUBTOTAL
01-SEP-2017 AAA         1100 AAA
01-SEP-2017 BBB         1000 BBB
01-SEP-2017 CCC         1400 CCC
01-SEP-2017             3500 SUBTOTAL

8 rows selected.

demo@ORA12C> select *
  2  from (
  3  select dttm_month,appl,sum(mins) sum_mins,
  4   case grouping_id(appl)
  5   when 1 then 'SUBTOTAL'
  6   else appl
  7   end grp
  8  from test_grp_total
  9  group by dttm_month,rollup(appl)
 10       )
 11  where appl <> 'AAA' or appl is null
 12  /

DTTM_MONTH  APPL    SUM_MINS GRP
----------- ----- ---------- --------
01-AUG-2017 BBB          700 BBB
01-AUG-2017 CCC          700 CCC
01-AUG-2017             1800 SUBTOTAL
01-SEP-2017 BBB         1000 BBB
01-SEP-2017 CCC         1400 CCC
01-SEP-2017             3500 SUBTOTAL

6 rows selected.

demo@ORA12C>


2)
demo@ORA12C> variable n number
demo@ORA12C> exec :n :=2;

PL/SQL procedure successfully completed.

demo@ORA12C>
demo@ORA12C> select dttm_month, appl, sum_mins,grp
  2  from (
  3  select dttm_month,appl,sum(mins) sum_mins,
  4   case grouping_id(appl)
  5   when 1 then 'SUBTOTAL'
  6   else appl
  7   end grp,
  8   dense_rank() over( partition by dttm_month
  9        order by decode(appl,null,0,1) desc,sum(mins) desc ) r
 10  from test_grp_total
 11  group by dttm_month,rollup(appl)
 12       )
 13  where r <= :n or appl is null
 14  /

DTTM_MONTH  APPL    SUM_MINS GRP
----------- ----- ---------- --------
01-AUG-2017 BBB          700 BBB
01-AUG-2017 CCC          700 CCC
01-AUG-2017 AAA          400 AAA
01-AUG-2017             1800 SUBTOTAL
01-SEP-2017 CCC         1400 CCC
01-SEP-2017 AAA         1100 AAA
01-SEP-2017             3500 SUBTOTAL

7 rows selected.

demo@ORA12C>

Suresh Harvu, October 16, 2017 - 3:02 pm UTC

The solution works very well. There is a requirement though to give cumulative values for each month. So the result expected is ( the CUM_MINS is the new column needed in the result)

DTTM_MONTH APPL SUM_MINS GRP CUM_MINS
8/1/2017 BBB 700 BBB 700
8/1/2017 AAA 400 AAA 1100
8/1/2017 1800 SUBTOTAL
9/1/2017 AAA 1100 AAA 1100
9/1/2017 BBB 1000 BBB 2100
9/1/2017 3500 SUBTOTAL

It is the cumulative value of SUM_MINS for each month. I tried to use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) to get a cumulative value grouped by DTTM_MONTH but not able to generate results properly.

Can you please suggest.
Chris Saxon
October 16, 2017 - 4:17 pm UTC

What happened to your CCC rows? Why have they disappeared?

Suresh Harvu, October 16, 2017 - 4:42 pm UTC

As I mentioned, the APPL values can be chosen. So to satisfy the requirement that the SUB_tOTAL has the value of ALL APPL values ( irrespective of the ones chosen ) , I removed CCC from the IN list.

I used this query:

select *
from (
select dttm_month,appl,sum(mins) sum_mins,
case grouping_id(appl)
when 1 then 'SUBTOTAL'
else appl
end grp
from test_grp_total
group by dttm_month,rollup(appl)
order by dttm_month, grouping_id(dttm_month,appl), sum(mins) desc
)
where appl IN ('AAA','BBB') or appl is null.

What I am looking for is the cumulative value for the SUM_MINS and by DTTM_MONTH.

Thanks again.
Chris Saxon
October 16, 2017 - 4:58 pm UTC

You're filtering the appl values in the outer query. So you get the subtotal by month for everything. Then restrict to a subset. But the SUBTOTALs include the CCC values!

Is this really what you want?

Looks fishy to me.

Anyway, to get running totals use the analytic version of sum in the outer query. e.g.:

select s.*, 
  sum(sum_mins) over (partition by dttm_month, grp order by grp, sum_mins desc) 
from ( 
select dttm_month,appl,sum(mins) sum_mins, 
case grouping_id(appl) 
when 1 then 'SUBTOTAL' 
else appl 
end grp 
from test_grp_total 
group by dttm_month,rollup(appl) 
order by dttm_month, grouping_id(dttm_month,appl), sum(mins) desc 
) s
where appl IN ('AAA','BBB') or appl is null;

DTTM_MONTH   APPL     SUM_MINS GRP          SUM(SUM_MINS)OVER(PARTITIONBYDTTM_MONTH,GRPORDERBYGRP,SUM_MINSDESC)
01-AUG-17    AAA           400 AAA                                                                          400
01-AUG-17    BBB           700 BBB                                                                          700
01-AUG-17                 1800 SUBTOTAL                                                                    1800
01-SEP-17    AAA          1100 AAA                                                                         1100
01-SEP-17    BBB          1000 BBB                                                                         1000
01-SEP-17                 3500 SUBTOTAL                                                                    3500

Suresh Harvu, October 16, 2017 - 5:19 pm UTC

I do see your point. We are restricting the list to the values outside the main query. So SUB_TOTAL is correct that it includes ALL appl. But the cumulative value we just need for the appl that are in the outer IN list.

select s.*,
sum(sum_mins) over (partition by dttm_month, grp order by grp, sum_mins desc)
from (
select dttm_month,appl,sum(mins) sum_mins,
case grouping_id(appl)
when 1 then 'SUBTOTAL'
else appl
end grp
from test_grp_total
group by dttm_month,rollup(appl)
order by dttm_month, grouping_id(dttm_month,appl), sum(mins) desc
) s
where appl IN ('AAA','BBB') or appl is null;

This query produces the result correctly. It is the other column for cumulative values of sum_min and that need to be just for the outer query appl.


Chris Saxon
October 17, 2017 - 10:57 am UTC

It is the other column for cumulative values of sum_min and that need to be just for the outer query appl.

I'm not sure what you mean?

Suresh Harvu, October 17, 2017 - 1:37 pm UTC

In my test case, I have 3 appl values : AAA,BBB,CCC

What I am looking for is the SUB_TOTAL to give the sum of all MIN values for ALL appl ( AAA,BBB,CCC) even if the query was filtering only for AAA,BBB. The query that you provided works correctly for this requirement. The filter in the last line of the query:
where appl IN ('AAA','BBB') or appl is null;
produced the result.

What I am looking for is a column in the result set that will produce cumulative value of the SUM(MIN) partitioned by month.

This is how the result is currently coming:

8/1/2017 BBB 700 BBB
8/1/2017 AAA 400 AAA
8/1/2017 1800 SUBTOTAL
9/1/2017 AAA 1100 AAA
9/1/2017 BBB 1000 BBB
9/1/2017 3500 SUBTOTAL

What I am looking for is: (Please see the CUM_MIN that is cumulative for each month separately)

DTTM_MONTH APPL SUM(MIN) CUM_MIN
8/1/2017 BBB 700 BBB 700
8/1/2017 AAA 400 AAA 1100
8/1/2017 1800 SUBTOTAL
9/1/2017 AAA 1100 AAA 1100
9/1/2017 BBB 1000 BBB 2100
9/1/2017 3500 SUBTOTAL


Thanks a lot for working this for me.
Chris Saxon
October 17, 2017 - 2:45 pm UTC

I partitioned by the wrong column in the previous SQL. Partition by the result of the grouping_id function to split the totals up as needed:

select s.*, 
  sum(sum_mins) over (
    partition by dttm_month, grp_id order by sum_mins desc
  ) running_tot
from ( 
select dttm_month,appl,sum(mins) sum_mins, 
case grouping_id(appl) 
when 1 then 'SUBTOTAL' 
else appl 
end grp ,
grouping_id(appl) grp_id
from test_grp_total 
group by dttm_month,rollup(appl) 
order by dttm_month, grouping_id(dttm_month,appl), sum(mins) desc 
) s
where appl IN ('AAA','BBB') or appl is null;

DTTM_MONTH             APPL   SUM_MINS   GRP        GRP_ID   RUNNING_TOT   
01-AUG-2017 00:00:00   BBB           700 BBB               0           700 
01-AUG-2017 00:00:00   AAA           400 AAA               0          1100 
01-AUG-2017 00:00:00                1800 SUBTOTAL          1          1800 
01-SEP-2017 00:00:00   AAA          1100 AAA               0          1100 
01-SEP-2017 00:00:00   BBB          1000 BBB               0          2100 
01-SEP-2017 00:00:00                3500 SUBTOTAL          1

Suresh Harvu, October 17, 2017 - 5:56 pm UTC

That's an awesome solution. Worked perfect.

Thanks a ton.
SUresh

Slight syntax variant

Stew Ashton, October 17, 2017 - 9:54 pm UTC

Seriously fine answer! Just for fun, I made a few syntax changes to say "select" just once.
select dttm_month, appl, sum(mins) sum_mins, 
  case grouping_id(appl) 
    when 1 then 'SUBTOTAL' 
    else appl 
  end grp,
  grouping_id(appl) grp_id,
  sum(sum(mins)) over (
    partition by dttm_month, grouping_id(appl) order by sum(mins) desc
  ) running_tot
from test_grp_total 
group by dttm_month, rollup(appl) 
having appl IN ('AAA','BBB') or appl is null;

DTTM_MONTH   APPL  SUM_MINS  GRP       GRP_ID  RUNNING_TOT  
01-aug-2017  BBB   700       BBB       0       700          
01-aug-2017  AAA   400       AAA       0       1100         
01-aug-2017        1800      SUBTOTAL  1       1800         
01-sep-2017  AAA   1100      AAA       0       1100         
01-sep-2017  BBB   1000      BBB       0       2100         
01-sep-2017        3500      SUBTOTAL  1       3500

Best regards, Stew
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.