求和SQL中列不同的所有行

Question

我有一个简单的表。

相关字段为：退货值和退货编号

因此，此表显示了返回的所有项目，此返回的返回编号，以及此返回中所有项目的值。

所以一个示例表可以看起来像这个

Line # | Item Number | Quantity Returned | Return Value | Return Number | Cust Order #
1        789            1                   $40             123          456
1        780            1                   $40             123          456
1        780            1                   $20             124          456

我只想用不同的返回数字来总结所有的返回值。例如，有两行的返回编号为123，一行的返回号为124。所以它应该取123中的一个，加起来就是124，给我60美元的

我试过

SUM((rh.Total_Value-rh.Freight_Charges)) OVER (PARTITION BY  rh.Customer_Purchase_Order_Number) as Total_Returned_Value 
SUM((rh.Total_Value-rh.Freight_Charges)) OVER (PARTITION BY rh.Return_Number) as Total_Returned_Value 
SUM((rh.Total_Value-rh.Freight_Charges)) OVER (PARTITION BY rh.Return_Number Order by rh.Customer_Purchase_Order_Number) as Total_Returned_Value 
SUM((rh.Total_Value-rh.Freight_Charges)) OVER (PARTITION BY rh.Customer_Purchase_Order_Number Order by rh.Return_Number) as Total_Returned_Value 

这些似乎都不起作用，我觉得我对的订单和分区没有很好的把握

这是我的完整代码

select  rh.Return_Number,
rd.Odet_Line_Number, rd.Item_Number, rd.Color_Code, rd.Quantity_Returned,
(rh.Total_Value-rh.Freight_Charges)as Returned_Value, rh.Remarks,
SUM((rh.Total_Value-rh.Freight_Charges)) OVER (PARTITION BY /*rh.Return_Number Order by*/ rh.Customer_Purchase_Order_Number) as Total_Returned_Value 
from
[JMNYC-AMTDB].[AMTPLUS].[dbo].Returns_Header rh (nolock)
LEFT JOIN
[JMNYC-AMTDB].[AMTPLUS].[dbo].Returns_Detail rd (nolock) on rd.Return_Number = Rh.Return_number
WHERE rh.Customer_Purchase_Order_Number = @Shopify

Answer 1

每个标头可能有多个详细信息行，导致标头数据重复。如果你想通过唯一的返回数求和，那么首先在CTE中对标题进行计算，并将结果与细节联系起来，就像这个一样

with rh as 
( select -- assuming the rh.Return_Number is unique
rh.Return_Number, 
(rh.Total_Value-rh.Freight_Charges)as Returned_Value,
rh.Remarks,
SUM((rh.Total_Value-rh.Freight_Charges)) 
OVER (PARTITION BY rh.Customer_Purchase_Order_Number) as Total_Returned_Value 
-- don't know if this is the PARTITION you want, maybe none
from
[JMNYC-AMTDB].[AMTPLUS].[dbo].Returns_Header rh (nolock)
)
select  rh.Return_Number,
rd.Odet_Line_Number, rd.Item_Number, rd.Color_Code, rd.Quantity_Returned,
rh.Returned_Value, rh.Remarks,
rh.Total_Returned_Value 
from
rh
LEFT JOIN
[JMNYC-AMTDB].[AMTPLUS].[dbo].Returns_Detail rd (nolock) on rd.Return_Number = Rh.Return_number
WHERE rh.Customer_Purchase_Order_Number = @Shopify