October 2, 2005 at 12:02 pm
The Problem:
1. I have say 20 records for a given Purchase Order in Purchase Order Item table.
2. I want to develop sum of ProductAmt, DiscountAmt, TaxAmount and update a record(already existing in POSumTable).
How do I do it?
I tried the following:
update POSumTable
set ProductTotal = sum(b.Productamt),
DiscountTotal = sum(b.DiscountAmt)
TaxTotal = sum(b.TaxAmount)
from ItemTable as b where b.POno= 23
But, it does not seem to be working.
Please guide.
Regards,
Dilip Nagle
October 3, 2005 at 12:51 am
Update POSumTable
set ProductTotal = SumOfAmount,
DiscountTotal = SumOfDiscount,
TaxTotal = SumOfTax
FROM (SELECT POno, sum(b.Productamt) AS SumOfAmount, sum(b.DiscountAmt) AS SumOfDiscount, sum(b.TaxAmount) AS SumOfTax
FROM ItemTable
GROUP BY PONo
) SumOfProducts
WHERE SumOfProducts.POno = POSumTable.POno
You can add an additional filter in the where clause to limit it to updating only one product.
Please give this a try and see if it works, if not, please post table structure and example data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 3, 2005 at 11:48 am
-- try following
UPDATE A
SET ProductTotal = SUM(B.Productamt), DiscountTotal = SUM(B.DiscountAmt), TaxTotal = SUM(B.TaxAmount)
FROM POSumTable A INNER JOIN ItemTable B ON A.PONo = B.PONo
WHERE A.PONo= 23
October 4, 2005 at 1:02 am
Server: Msg 157, Level 15, State 1, Line 2
An aggregate may not appear in the set list of an UPDATE statement.
I thought of that first too, but a quick test proved it wouldn't work. That's why I used a subquery.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply