Prev: SELECT SUBSTRING
Next: Convert Positive Value to Negative Value
From: Mary Phelps on 8 Mar 2010 12:36
I would like to get sum of the price for all items from table
ProductVariant
CREATE TABLE #Kits
(
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
x.InventoryVariantId=y.skusuffix

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 8 Mar 2010 12:57
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:

UPDATE #Kits
SET SumTotal = (SELECT MAX(y.Price)
FROM ProductVariant AS y
WHERE #Kits.InventoryVariantId = y.skusuffix)
WHERE EXISTS(SELECT *
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
http://www.SQLStudio.com
From: Mary Phelps on 8 Mar 2010 13:07
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 8 Mar 2010 13:18
Not exactly sure what your goal is, but try this:

UPDATE #Kits
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
http://www.SQLStudio.com
 | 
Pages: 1
Prev: SELECT SUBSTRING
Next: Convert Positive Value to Negative Value