Return only one record from a Grouped Table Join
Hello
I have an orders table that has duplicate products on the same order.
I Need to ADD the duplicate Qtys together then return one of the Order Line ID's
I have Grouped the Table by the Order ID and the Stock Item but how do I get the Line ID of One of the Lines?
SELECT SOL.SEQNO,SOL.HDR_SEQNO, SOL.STOCKCODE, SUM(SOL.ORD_QUANT)
FROM dbo.SALESORD_LINES AS SOL
WHERE SOL.HDR_SEQNO = 205474
GROUP BY SOL.SEQNO,SOL.HDR_SEQNO,SO
This Returns
SEQNO HDR_SEQNO STOCKCODE ORDER_QTY
86616 205474 A101487 1
86617 205474 A101488 1
86618 205474 A101489 1
86619 205474 A103866 10
86620 205474 A103867 10
86621 205474 X100246 1
86622 205474 X100246 1
What I need is
SEQNO HDR_SEQNO STOCKCODE ORDER_QTY
86616 205474 A101487 1
86617 205474 A101488 1
86618 205474 A101489 1
86619 205474 A103866 10
86620 205474 A103867 10
86621 205474 X100246 2
(The last two lines have the Order_qty added and one of the seqno records)
Note the "WHERE SOL.HDR_SEQNO = 205474" is only to reduce the records to one order for a sample - The code is to go in a view with thousands of orders
I have an orders table that has duplicate products on the same order.
I Need to ADD the duplicate Qtys together then return one of the Order Line ID's
I have Grouped the Table by the Order ID and the Stock Item but how do I get the Line ID of One of the Lines?
SELECT SOL.SEQNO,SOL.HDR_SEQNO, SOL.STOCKCODE, SUM(SOL.ORD_QUANT)
FROM dbo.SALESORD_LINES AS SOL
WHERE SOL.HDR_SEQNO = 205474
GROUP BY SOL.SEQNO,SOL.HDR_SEQNO,SO
This Returns
SEQNO HDR_SEQNO STOCKCODE ORDER_QTY
86616 205474 A101487 1
86617 205474 A101488 1
86618 205474 A101489 1
86619 205474 A103866 10
86620 205474 A103867 10
86621 205474 X100246 1
86622 205474 X100246 1
What I need is
SEQNO HDR_SEQNO STOCKCODE ORDER_QTY
86616 205474 A101487 1
86617 205474 A101488 1
86618 205474 A101489 1
86619 205474 A103866 10
86620 205474 A103867 10
86621 205474 X100246 2
(The last two lines have the Order_qty added and one of the seqno records)
Note the "WHERE SOL.HDR_SEQNO = 205474" is only to reduce the records to one order for a sample - The code is to go in a view with thousands of orders
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Jim Horn
Looks like chaau has directly answered your question, so I won't propose another answer, but as an aside if you want to read some articles related to what you're doing here, check out SQL Server GROUP BY Solutions and SQL Server Delete Duplicate Rows Solutions.