select distinct
quantity,
standard_cost,
sum(a.quantity*a.standard_cost) over(partition by purchase_scence_code,raw_material_code) amount,
coalesce(purchase_scence_code,'unknown') as purchase_scence_code,
coalesce(raw_material_code,'unknown') as raw_material_code
from ap.fact_outbound_order as a
where
a.material_type_code='3'
and
quantity<0
select
purchase_scence_code,
sum(amount)
from (
select distinct
quantity,
standard_cost,
sum(a.quantity*a.standard_cost) over(partition by purchase_scence_code,raw_material_code) amount,
coalesce(purchase_scence_code,'unknown') as purchase_scence_code,
coalesce(raw_material_code,'unknown') as raw_material_code
from ap.fact_outbound_order as a
where a.material_type_code='3'
) t1
group by t1.purchase_scence_code
