CODEWITHSUNDEEP
mysqlsqlselectaverage
TheFurgeurge
TheFurgeurge

Reputation: 23

Trigger or event to get average from another table

ratings_table
-------------------------
id(PK)  color(FK)  rating
=========================
1       green      5  
2       green      2
3       blue       4
4       yellow     4
5       blue       3

colors_table:
---------------------
color(PK)  avg_rating
=====================
green      ?  
blue       ?
yellow     ?

How can I set the average rating of each color? I would like to change it automatically when a new data inserted into the rating_table or a record updated.

I guess it quite easy, but I have no idea how to do it.

Upvotes: 1

Views: 51

Answers (1)

Mureinik
Mureinik

Reputation: 312008

The easiest approach would be to have colors_table as a view instead of a table where you need to mess around with triggers:

CREATE VIEW colors AS
SELECT   color, AVG(rating)
FROM     ratings_table
GROUP BY color

Upvotes: 1

Related Questions