question

suja avatar image
suja asked

how to store value of avg(col1) into col2 in same table

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

sql-server-2005t-sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

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
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Doh.. You bet me to the same response... Dam my need for coffee! + 1 matt
1 Like 1 ·
Guys he has tagged this with 'table-valued' do you think he might want it doing with a function perhaps?
0 Likes 0 ·
@Ian - perhaps, but would the code be any different for a table-valued function? And would it make any more sense? I'm thinking 'no' on both counts...
0 Likes 0 ·
incorrectly tagged - sorted now
0 Likes 0 ·
TimothyAWiseman avatar image
TimothyAWiseman answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.