oracle sql question on aggregation of transaction data

I am trying to add everything in te data (dollar_value_us, QUANTITY) but not add shipping total since each transaction number has multiple items but the customer only paid shipping once. I am using the below data:

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=01693db7ce05b062804cedeb3b3a7e73

below is the query which I am using on my actual databse:

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

the final output for usa should have shipping total amount of 35

Answer

Try this:

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)c
  group by quarter_date,country
)a
join -- Below is part of your query
(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

Test Result:

DB<>Fiddle