Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table transaction_detail_mv (
    individual_id int,
    dollar_value_us int,
    quantity int,
    transaction_number int,
    txn_date date,
    brand_org_code varchar(5),
    is_merch int,
    currency_code varchar(5),
    line_item_amt_type_cd varchar(1),
    shipping_total int,
    country varchar(5),
    QUARTER_DATE varchar(5)
    );
insert into transaction_detail_mv values(1, 10, 30,12345, TO_DATE('01-02-2019', 'DD-MM-YYYY'), 'BRAND', 1, 'USD', 'S', 10, 'USA','QT1');
1 rows affected

insert into transaction_detail_mv values(1, 10,40,12345, TO_DATE('10-02-2019', 'DD-MM-YYYY'), 'BRAND', 1, 'USD', 'S',10,'USA','QT1'); 
1 rows affected

insert into transaction_detail_mv values(1, 10, 50,12345,TO_DATE('15-02-2019', 'DD-MM-YYYY'), 'BRAND', 1, 'USD', 'S',10, 'USA','QT1');
1 rows affected

insert into transaction_detail_mv values(1, 10,70,975, TO_DATE('28-02-2019', 'DD-MM-YYYY'), 'BRAND', 1, 'USD', 'S',20,'ROU','QT1');
1 rows affected

insert into transaction_detail_mv values(2, 11,80,975, TO_DATE('11-02-2019', 'DD-MM-YYYY'), 'BRAND', 1, 'USD', 'S',20,'AFR','QT2');
1 rows affected

insert into transaction_detail_mv values(2, 11, 90,323, TO_DATE('12-02-2019', 'DD-MM-YYYY'), 'BRAND', 1, 'USD', 'S',15,'IND','QT2');
1 rows affected

insert into transaction_detail_mv values(3, 11, 62,855, TO_DATE('12-02-2019', 'DD-MM-YYYY'), 'BRAND', 1, 'USD', 'S',25,'USA','QT1');
1 rows affected

select * from transaction_detail_mv


INDIVIDUAL_ID
DOLLAR_VALUE_US
QUANTITY
TRANSACTION_NUMBER
TXN_DATE
BRAND_ORG_CODE
IS_MERCH
CURRENCY_CODE
LINE_ITEM_AMT_TYPE_CD
SHIPPING_TOTAL
COUNTRY
QUARTER_DATE


1
10
30
12345
01-FEB-19
BRAND
1
USD
S
10
USA
QT1

1
10
40
12345
10-FEB-19
BRAND
1
USD
S
10
USA
QT1

1
10
50
12345
15-FEB-19
BRAND
1
USD
S
10
USA
QT1

1
10
70
975
28-FEB-19
BRAND
1
USD
S
20
ROU
QT1

2
11
80
975
11-FEB-19
BRAND
1
USD
S
20
AFR
QT2

2
11
90
323
12-FEB-19
BRAND
1
USD
S
15
IND
QT2

3
11
62
855
12-FEB-19
BRAND
1
USD
S
25
USA
QT1


select distinct transaction_number,sum(DOLLAR_VALUE_US), sum(QUANTITY), sum(SHIPPING_TOTAL)
from transaction_detail_mv
group by TRANSACTION_NUMBER


TRANSACTION_NUMBER
SUM(DOLLAR_VALUE_US)
SUM(QUANTITY)
SUM(SHIPPING_TOTAL)


12345
30
120
30

975
21
150
40

323
11
90
15

855
11
62
25


select QUARTER_DATE ,COUNTRY,sum(DOLLAR_VALUE_US), sum(QUANTITY), sum(SHIPPING_TOTAL)
from transaction_detail_mv
group by QUARTER_DATE,COUNTRY


QUARTER_DATE
COUNTRY
SUM(DOLLAR_VALUE_US)
SUM(QUANTITY)
SUM(SHIPPING_TOTAL)


QT1
USA
41
182
55

QT1
ROU
10
70
20

QT2
AFR
11
80
20

QT2
IND
11
90
15


select a.quarter_date, a.country, a.total_shipping, b.total_dollar_value, b.total_quantity
from 
(select quarter_date, country, sum(shipping_total) as total_shipping
from (
select distinct quarter_date, country, shipping_total
from transaction_detail_mv
)
group by quarter_date,country
)a
join 
(select QUARTER_DATE ,COUNTRY,sum(DOLLAR_VALUE_US) as total_dollar_value, sum(QUANTITY) as total_quantity
from transaction_detail_mv
group by QUARTER_DATE,COUNTRY)b
on a.quarter_date = b.quarter_date
and a.country = b.country




QUARTER_DATE
COUNTRY
TOTAL_SHIPPING
TOTAL_DOLLAR_VALUE
TOTAL_QUANTITY


QT1
USA
35
41
182

QT1
ROU
20
10
70

QT2
AFR
20
11
80

QT2
IND
15
11
90

INDIVIDUAL_ID	DOLLAR_VALUE_US	QUANTITY	TRANSACTION_NUMBER	TXN_DATE	BRAND_ORG_CODE	IS_MERCH	CURRENCY_CODE	LINE_ITEM_AMT_TYPE_CD	SHIPPING_TOTAL	COUNTRY	QUARTER_DATE
1	10	30	12345	01-FEB-19	BRAND	1	USD	S	10	USA	QT1
1	10	40	12345	10-FEB-19	BRAND	1	USD	S	10	USA	QT1
1	10	50	12345	15-FEB-19	BRAND	1	USD	S	10	USA	QT1
1	10	70	975	28-FEB-19	BRAND	1	USD	S	20	ROU	QT1
2	11	80	975	11-FEB-19	BRAND	1	USD	S	20	AFR	QT2
2	11	90	323	12-FEB-19	BRAND	1	USD	S	15	IND	QT2
3	11	62	855	12-FEB-19	BRAND	1	USD	S	25	USA	QT1