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>
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
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.
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.
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.
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.
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.
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