Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

INSERT INTO AfricanRatingsAVG
SELECT RecipeID,
Count(*) AS cnt,
AVG(Rating)
FROM AfricanRatings
GROUP BY RecipeID
ORDER BY RecipeID

to get the average rating per recipe and the total count per recipeID. The average and the count were placed into a table AfricanRatingsAVG via the query. On the initial query, AfricanRatingsAVG is updated but not with subsequent ratings. I have tried all sorts of UPDATES to get the AfricanRatingAVG table to update every time a new rating is added to the AfricanRatings table, however, no update occurs. Any guidance in the right direction would be appreciated.

I must be doing something wrong. Got this error message:Unable to parse query text. The query cannot be represented graphically in the Diagram and Criteria Pane.
–
Earl DavisFeb 6 '13 at 22:32

This is not your query. This is the DDL you run to create the view, from your favorite management tool or from your deployment script. Your query designed will be SELECT ... FROM AfricanRatingsAVG WITH (NOEXPAND)
–
Remus RusanuFeb 6 '13 at 22:39

1

Should be SUM instead of AVG in the view definition. (Well, not exactly, but AVG is disallowed; you get what I mean hopefully.)
–
Jon SeigelFeb 7 '13 at 2:33