Sum all rows where column is different in SQL?

Question

I have a simple table.

The relevant fields are:Return Value, and Return Number

So this table shows me all items that were returned, what return number this return is, and what was the value of all items in this return.

So an example table can look something like this

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

I just want it to sum up all return values by different return numbers. So for example, there are two rows with return number 123 and one row with return number 124. So it should take one of the 123 and sum it to 124, giving my $60

I've tried

    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

None of these seem to work and I feel that I don't have a great grasp on order by and partition by

This is my full code

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

why not group by? something like this [link](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — Pablo Ferro, Jan 08 '19 at 18:51
Try partitioning by `Item_Number` and `Cust Order #` without `ORDER BY`. See [this articles series](https://www.itprotoday.com/microsoft-sql-server/how-use-microsoft-sql-server-2012s-window-functions-part-1), [this](https://www.red-gate.com/simple-talk/sql/t-sql-programming/t-sql-window-functions-and-performance/) and [this](https://www.brentozar.com/sql-syntax-examples/window-function-examples-sql-server/) to understand how they work. — Marc0, Jan 08 '19 at 18:57
You got multiple lines per header resulting in duplicate data. Do the calculation on the header first in a CTE and join the result to the lines. — dnoeth, Jan 08 '19 at 18:58
You only want one row for each Return_Number, correct? Then i suggest you take a look at Row_Number(). Something similar to `select * from (select *,Row_Number() over (PARTITION BY rh.Return_Number) as row_number from TABLE_NAME) tb where tb.row_number=1` From the top of my head, so only use it to get inspired :) — MikNiller, Jan 08 '19 at 18:58
@Marc0 Cust Order # will be the same for every row. So I should partition by whatever each return has different, and then partition by what all the rows have the same? — Natan, Jan 08 '19 at 19:00
The problem is that you want to aggregate an aggregate, so I don't think you can do it with a single OVER() clause. I think you'll have to take a tiered approach with a CTE. — Tab Alleman, Jan 08 '19 at 19:01

score 0 · Accepted Answer · answered Jan 08 '19 at 19:06

You probably got multiple detail rows per header resulting in duplicate header data. If you want to sum by unique return number do the calculation on the header first in a CTE and join the result to the detail, like this

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

Yep, that fixed the problem. Thank you. – Natan Jan 08 '19 at 19:14 — Natan, Jan 08 '19 at 19:14

Sum all rows where column is different in SQL?

1 Answers1