Take the 2-minute tour ×
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.

I have a database AfricanRatings on which I used the query

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.

share|improve this question
    
What have you tried? What do[es] your UPDATE attempt(s) look like? –  Thomas Stringer Feb 6 '13 at 21:03
    
UPDATE AfricanRatingsAVG SET AfricanRatingsAVG.RecipeID = AfricanRatings.RecipeID, AfricanRatingsAVG.cnt = AfricanRatings.Count(), AfricanRatingsAVG.Expr1 = AfricanRatings.AVG(Rating) FROM AfricanRatings INNER JOIN AfricanRatingsAVG ON AfricanRatingsAVG = AfricanRatings UPDATE AfricanRatingsAVG SET AfricanRatingsAVG.RecipeID = AfricanRatings.RecipeID, AfricanRatingsAVG.cnt = AfricanRatings.Count() AS cnt, AfricanRatingsAVG.Expr1 = AfricanRatings.AVG(Rating) AS Expr1 FROM AfricanRatings INNER JOIN AfricanRatingsAVG ON –  Earl Davis Feb 6 '13 at 22:05
2  
Add the UPDATEs as edits to your original post, not as comments. –  Remus Rusanu Feb 6 '13 at 22:41

1 Answer 1

Use an indexed view instead:

CREATE VIEW AfricanRatingsAVG
WITH SCHEMABINDING
AS
SELECT RecipeID,
       COUNT_BIG(*) AS cnt,
       AVG(Rating) as rating
FROM   dbo.AfricanRatings
GROUP  BY RecipeID;
GO

CREATE UNIQUE CLUSTERED INDEX cdxAfricanRatingsAVG on AfricanRatingsAVG(RecipeID);

The engine will maintain the cnt and avg for you. Use the NOEXPAND hint when querying the view:

When NOEXPAND is specified for a view, the query optimizer considers using any indexes defined on the view.

share|improve this answer
    
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 Davis Feb 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 Rusanu Feb 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 Seigel Feb 7 '13 at 2:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.