Tuesday, 10 September 2013

Sum a column then group based on another column that has duplicate values (SQL 2005)

Sum a column then group based on another column that has duplicate values
(SQL 2005)

SELECT ShipDate, Stockroom, COLineNumber, CONumber, ShippedQty,
ReversedQty, CASE WHEN ReversedQty IS NULL THEN ShippedQty ELSE
ReversedQty * - 1 END AS FinalShippedQty
FROM MyTable
WHERE CONumber='RAN-00001000'
This is my current output based on the query above:
ShipDate Stockroom CoLineNumber CONumber ShippedQty ReversedQty
FinalShippedQty



8/7/2013 01 1 RAN-00001000 10 NULL 10 8/7/2013 41 2 RAN-00001000 12 NULL
12 8/7/2013 41 3 RAN-00001000 24 NULL 24 8/7/2013 41 4 RAN-00001000 12
NULL 12 8/7/2013 41 4 RAN-00001000 NULL 2 -2 8/7/2013 01 5 RAN-00001000 50
NULL 50 8/7/2013 41 6 RAN-00001000 12 NULL 12 8/7/2013 41 6 RAN-00001000
NULL 3 -3 8/7/2013 41 6 RAN-00001000 NULL 3 -3
As you notice above that CoLineNumber 4 is twice because we had an
original entry then reversed the Qty by 2 and then CoLineNumber 6 has
three entries for the same cause. I would like to Summarize the
FinalShippedQty Column for those rows that have the same CoLineNumber and
belong to the same CONumber. This is only one order here but usually the
true extract could quite a few.
Here is the output I would like to see:
ShipDate Stockroom CoLineNumber CONumber FinalShippedQty



8/7/2013 01 1 RAN-00001000 10 8/7/2013 41 2 RAN-00001000 12 8/7/2013 41 3
RAN-00001000 24 8/7/2013 41 4 RAN-00001000 10 8/7/2013 01 5 RAN-00001000
50 8/7/2013 41 6 RAN-00001000 6
Please let me know if you have any questions or need further explanation.
again keep in mind that the table I have has hundreds of CoNumbers but I
just want to Sum up the FinalShippedQty for those CoLineNumbers that are
equal and belong to that specific CONumber.
Thanks
EDIT: I've added the Stockroom column which I didn't have the first time.

No comments:

Post a Comment