Hi Connor, Chris,
Could you please help me to build SQL for below scenario
I have a orders table with order_loc, I want the output of SQL based on distinct order_ccy for order_loc and at the end of each order_loc, I need to summarise order_ccy count like below:
"SB" is the hard coded value for each row and "SF" is hardcoded value for summation of distinct order_ccy
SB AA1 1 USD 200
SB AA1 3 USD 300
SB AA1 2 JPY 250
SF USD 500
SF JPY 250
SB BB1 4 USD 200
SB BB1 5 USD 300
SF USD 500
create table orders
(
order_id number,
order_name varchar2(20 char),
order_amt number,
order_ccy varchar2(20 char),
order_loc varchar2(20 char)
)
/
insert into orders values (1,'A',200,'USD','AA1');
insert into orders values (2,'B',250,'JPY','AA1');
insert into orders values (3,'C',300,'USD','AA1');
insert into orders values (4,'D',400,'USD','BB1');
insert into orders values (5,'E',500,'USD','BB1');
You can use ROLLUP, CUBE, & GROUPING SETS to get subtotals for various combinations
The requested output doesn't seem to match the sample data. But it seems you want to get the subtotals for:
- Each ID, LOC, & CURRENCY
- Each LOC & CURRENCY
So put these in GROUPING SETS in the group by:
select case
when order_id is null then 'SF'
else 'SB'
end summation,
order_id,
order_loc, order_ccy, sum ( order_amt )
from orders
group by grouping sets (
( order_id, order_ccy, order_loc ),
( order_ccy, order_loc )
)
order by order_loc, order_id nulls last;
SUMMATION ORDER_ID ORDER_LOC ORDER_CCY SUM(ORDER_AMT)
SB 1 AA1 USD 200
SB 2 AA1 JPY 250
SB 3 AA1 USD 300
SF <null> AA1 USD 500
SF <null> AA1 JPY 250
SB 4 BB1 USD 400
SB 5 BB1 USD 500
SF <null> BB1 USD 900