Hi Tom
I was looking through the already asked questions about Subtotals and Grand totals , but couldn't really find anything that suits my problem.
I have a table with the following fields
DATE REGION REG_NUM AMOUNT
01-MAR-19 REG01 A01 1.00
02-MAR-19 REG01 B01 10.00
03-MAR-19 REG03 Z01 500.00
03-MAR-19 REG04 C01 9500.00
04-MAR-19 REG01 A01 20.00
10-MAR-19 REG03 E01 35.50
11-MAR-19 REG03 E01 20.25
19-MAR-19 REG04 C01 1000.00
17-MAR-19 REG01 B01 15.00
My output has to order by REGION, but show subtotal on REG_NUM level:
DATE REGION REG_NUM AMOUNT
01-MAR-19 REG01 A01 1.00
04-MAR-19 REG01 A01 20.00
TOTAL 21.00
02-MAR-19 REG01 B01 10.00
17-MAR-19 REG01 B01 15.00
TOTAL 25.00
03-MAR-19 REG03 Z01 500.00
TOTAL 500.00
10-MAR-19 REG03 E01 35.50
11-MAR-19 REG03 E01 20.25
TOTAL 55.25
03-MAR-19 REG04 C01 9500.00
19-MAR-19 REG04 C01 1000.00
10500.00This is what I've tried. I am not sure how to add REGION to the ORDER BY as it messes up the result:
SELECT DATE, REGION, REG_NUM, SUM(AMOUNT)
FROM MY_TRANSACTIONS
GROUP BY GROUPING SETS ( (DATE, REGION, REG_NUM, AMOUNT) , REG_NUM , () )
ORDER BY REG_NUM NULLS LAST;
Thank you
Here's one approach, assuming that each region can appear in only one reg_num:
Create a custom sort by:
- Using the grouping_id function to determine which subtotal level you're at
- If this line-level total, return the current region
- If it's the grand total, return null
- If it's the reg_num subtotal, then get the value for the last region by:
* Using last_value
* Partitioned by reg_num
* Ordered by the region and date, both nulls last
Which gives a case statement like:
case
when grouping_id ( region, reg_num ) = 2 then
last_value ( region ) ignore nulls over (
partition by reg_num
order by region nulls last, dt nulls last
)
when grouping_id ( region, reg_num ) = 3 then
null
else
region
end srtI've also added a row_number() with the same window clause to aid sorting.
Then sort by the custom function, region, and other columns as needed:
create table t (
dt date, region varchar2(10), reg_num varchar2(10), amt number
);
insert into t values ( date'2019-03-01', 'REG01', 'A01', 1.00 );
insert into t values ( date'2019-03-02', 'REG01', 'B01', 10.00 );
insert into t values ( date'2019-03-03', 'REG03', 'Z01', 500.00 );
insert into t values ( date'2019-03-03', 'REG04', 'C01', 9500.00 );
insert into t values ( date'2019-03-04', 'REG01', 'A01', 20.00 );
insert into t values ( date'2019-03-10', 'REG03', 'E01', 35.50 );
insert into t values ( date'2019-03-11', 'REG03', 'E01', 20.25 );
insert into t values ( date'2019-03-19', 'REG04', 'C01', 1000.00 );
insert into t values ( date'2019-03-17', 'REG01', 'B01', 15.00 );
select dt, region, reg_num, sum(amt),
grouping_id ( region, reg_num ) gid,
row_number() over (
partition by reg_num
order by region nulls last, dt nulls last
) rn,
case
when grouping_id ( region, reg_num ) = 2 then
last_value ( region ) ignore nulls over (
partition by reg_num
order by region nulls last, dt nulls last
)
when grouping_id ( region, reg_num ) = 3 then
null
else
region
end srt
from t
group by grouping sets ( (dt, region, reg_num, amt) , reg_num , () )
order by srt nulls last, reg_num, rn;
DT REGION REG_NUM SUM(AMT) GID RN SRT
01-MAR-2019 REG01 A01 1 0 1 REG01
04-MAR-2019 REG01 A01 20 0 2 REG01
<null> <null> A01 21 2 3 REG01
02-MAR-2019 REG01 B01 10 0 1 REG01
17-MAR-2019 REG01 B01 15 0 2 REG01
<null> <null> B01 25 2 3 REG01
10-MAR-2019 REG03 E01 35.5 0 1 REG03
11-MAR-2019 REG03 E01 20.25 0 2 REG03
<null> <null> E01 55.75 2 3 REG03
03-MAR-2019 REG03 Z01 500 0 1 REG03
<null> <null> Z01 500 2 2 REG03
03-MAR-2019 REG04 C01 9500 0 1 REG04
19-MAR-2019 REG04 C01 1000 0 2 REG04
<null> <null> C01 10500 2 3 REG04
<null> <null> <null> 11101.75 3 1 <null>