Count average cost and update cost of all products in column with conditions

[gelezniyden]

Hi guys, please help me with following:

I have table "Products" and have following task: Count evarege cost and update cost of all products in column "UnitPrice" with conditions if current price > everage then -10% if < +20%

By the way need to use variables

Please help me to write script I use SQL server 2000

Thanks everybody before.

[r035198x]

What have you tried?

[gelezniyden]

Somthing like that but don't know how to resume

DECLARE @Average cost
 
SELECT @Average cost = AVG(UnitPrice)  FROM Products  
 
 
Update Products
 
Set UnitPrice = @Average cost
 
DECLARE @Computing
 
IF

[r035198x]

Here is a tip: You can use a CASE clause in an update statement

e.g

UPDATE someTableName 
SET someColumnName = CASE WHEN (someColumnName < someValue) THEN value1 ELSE value2 END

Now try it again.

[gelezniyden]

Didn't get you buddy sorry I'm not very good in Transact SQL I've wrote somthing like below:

DECLARE @Average cost
 
SELECT @Average cost = AVG(UnitPrice)  FROM Products  
 
 
Update Products
 
Set UnitPrice = @Average cost
 
DECLARE @Computing
 
IF UnitPrice > @Average cost SET@Computing= -10%
 
ELSE SET IF UnitPrice < @Averaeg cost SET@Computing = +20%
 

Could you please correct my statment or modefy your according my conditions?

[r035198x]

Don't make variable names with spaces. Use AVERAGE_COST instead of Average cost for the variable name and indicate a type.


In your update you are setting UnitPrice to @Average which is not what you described above. Your description says:

"update cost of all products in column "UnitPrice" with conditions if current price > everage then -10% if < +20%" So your statement should look like

UPDATE Products
SET UnitPrice = CASE WHEN UnitPrice > @Average THEN <your logic here> ELSE <your other logic here> END
 

Trust me it's better for you learn how to come up with this yourself than have someone do it for you.

You can find more SQL server examples and tutorials here:http://msdn.microsoft.com/en-us/library/ms188927.aspx

[gelezniyden]

I wrote following:

UPDATE Products
SET UnitPrice = CASE WHEN UnitPrice > @Average_cost THEN -10% ELSE UnitPrice < @Average_cost THEN + 20%

Please correct if somthing is wrong

[r035198x]

What is -10% supposed to be? It's 10% of what? Also you can't just write plain English. You need to learn and use the correct SQL syntax. Hint: 10% of a value is the same as multiplying a value by 0.1

[gelezniyden]

Sorry buddy just can't understand it logically, anyway thank you for your help

[r035198x]

Just go through your question again and understand exactly what it's asking for. Think about how you would do it manually on a piece of paper until you really understand what's required. Then go through this thread again and see if it starts to make sense.

[gelezniyden]

Tried below:

DECLARE @Average_cost money(8)
 
SELECT @Average_cost = AVG(UnitPrice)  FROM Products 
 
UPDATE Products
SET UnitPrice = (CASE WHEN UnitPrice > @Average_cost 
                      THEN UnitPrice - UnitPrice/10 
                      ELSE UnitPrice < @Average_cost 
                      THEN UnitPrice - UnitPrice/20                      
                END)

It says Incorrect syntax near '<' I don't understand where I was wrong

[r035198x]

Look at the structure of the examples I posted and compare it with what you have.

[gelezniyden]

DECLARE @Average_cost money
 
SELECT @Average_cost = AVG(UnitPrice)  FROM Products  
UPDATE Products
SET UnitPrice = (CASE WHEN UnitPrice > @Average_cost 
                      THEN UnitPrice - (UnitPrice * .1)
                      WHEN UnitPrice < @Average_cost 
                      THEN UnitPrice + (UnitPrice * .2)  
                      ELSE @Average_cost                   
                END)

Thank you for help I applied statment above and it was helpful for me. Issue was resolved.

[r035198x]

Well done!