January 19, 2006 at 10:06 am
I have a report table that has these columns
Order# Line# Qty Total
a 1 2
a 2 5
a 3 3
b 1 6
c 1 10
c 2 4
I want to update the total column by sum of the qty grouped by order#. The result should be
Order# Line# Qty Total
a 1 2 10
a 2 5 10
a 3 3 10
b 1 6 6
c 1 10 14
c 2 4 14
Please help with my update query.
Thanks
January 19, 2006 at 10:40 am
select myTable.orderNum, myTable.LineNum, Qty, a.total from myTable inner join (select orderNum, sum(Qty) as total from myTable group by orderNum)a on myTable.ordernum = a.orderNum
**ASCII stupid question, get a stupid ANSI !!!**
January 19, 2006 at 10:47 am
This one way of doing it:
CREATE TABLE test
(
OrderNo CHAR(1),
Line INT,
Qty INT,
Total int
)
GO
INSERT INTO test ( OrderNo, Line, Qty) VALUES ('a', 1, 2)
INSERT INTO test ( OrderNo, Line, Qty) VALUES ('a', 2, 5)
INSERT INTO test ( OrderNo, Line, Qty) VALUES ('a', 3, 3)
INSERT INTO test ( OrderNo, Line, Qty) VALUES ('b', 1, 6)
INSERT INTO test ( OrderNo, Line, Qty) VALUES ('c', 1, 10)
INSERT INTO test ( OrderNo, Line, Qty) VALUES ('c', 2, 4)
GO
UPDATE test
SET
total = SumQty
FROM test INNER JOIN
( SELECT SUM(Qty) AS SumQty, OrderNo
FROM test
GROUP BY OrderNo) AS A ON A.OrderNo = test.OrderNo
GO
SELECT * FROM test
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply