hi
how to store value of avg(col1) into col2 in same table using Sql Server 2005 eg
calculating avg(rating) for Mcdonald and storing .......similarly for each restaurant table is like
Rest_Name Rating Average
Mcdonald 1 2avg(rating) where Rest_Name='Mcdonald'
Mcdonald 3 2
Pizza Hut 2 3avg(rating) where Rest_Name='Pizza Hut'
Pizza Hut 4 3
I want this output using single query or procedure or view ..........or anything
pls help
That doesn't make any sense - why would you want to store the average value of multiple rows into a single row, or repeat the value multiple times?
Senseless as it is, the code would be:
UPDATE table
SET col2 = (SELECT AVG(col1) FROM table)
This would put the average of col1 into col2 for all rows.
Edit -> After your edit, to do it for a sub-set, you want:
UPDATE tablename
SET col2 = (SELECT AVG(col1)
FROM tablename
WHERE restaurantName = tmain.tablename)
FROM tablename tMain
It still doesn't make a great deal of sense to do this, however. It would be much better to extract the average when you need it, instead of forcing every row to update each time any row updates. Something like:
SELECT restaurantName, col1, AVG(col1) OVER (PARTITION BY restaurantName)
FROM table
As always Matt's answer is excellent. Just for the sake of options, you could use a computer column. Something like:
create function dbo.restavg(@restname varchar(128))
returns float
as
begin
return (select avg(Rating)
from resttable
where restname = @restname)
end
GO
create table resttable(
restname varchar(128),
rating int,
average as dbo.restavg(restname))
Also remember that you can put something like Matt's windowed average query into a view. In fact, that is probably the best option overall.
