From: Mary Phelps on
I would like to get sum of the price for all items from table
ShoppingCartRecId int,
InventoryVariantId int,
SumTotal money
INSERT #Kits (ShoppingCartRecId,InventoryVariantId) select
ShoppingCartRecId,InventoryVariantId from Orders_KitCart
update x
set x.SumTotal=y.max([Price])
from #Kits as x inner join ProductVariant as y on

I get an error
Cannot find either column "y" or the user-defined function or
aggregate "y.sum", or the name is ambiguous.
From: Plamen Ratchev on
The table alias has to prefix the column, not the aggregate function. But even if you change it you cannot use aggregate
function in SET.

You can write the same query using an ANSI update statement:

SET SumTotal = (SELECT MAX(y.Price)
FROM ProductVariant AS y
WHERE #Kits.InventoryVariantId = y.skusuffix)
FROM ProductVariant AS y
WHERE #Kits.InventoryVariantId = y.skusuffix);

BTW, you mention sum and then use max in the query. If you need sum just replace MAX with SUM.

Plamen Ratchev
From: Mary Phelps on
This gives me the price. I am sorry for writing max. I need the sum of
all the items.
This is what I am getting from running your query.
4953 15600 1.09
4953 15603 1.39
4953 15606 2.99
4953 15607 5.49
4953 15620 4.99
4953 15635 3.99

What I need is
4953 15600 19.94
4953 15603 19.94
4953 15606 19.94
4953 15607 19.94
4953 15620 19.94
4953 15635 19.94
From: Plamen Ratchev on
Not exactly sure what your goal is, but try this:

SET SumTotal = (SELECT SUM(y.Price)
FROM ProductVariant AS y
JOIN #Kits AS x
ON x.InventoryVariantId = y.skusuffix
WHERE x.ShoppingCartRecId = #Kits.ShoppingCartRecId);

It may be better just to query the SUM on SELECT instead of maintaining and updating a column.

Plamen Ratchev