This is about rating poems according to a parameter such as style.
I have 3 tables, poems, style and rates.
Poems and style are FK linked.
styles table contains id, poem_id and value (being value what the score given by readers)
Rates is just a reference table that contains a translation of what a digit rating means, say, 1 means very poor, 10 excellent.
Each poem can receive many ratings from different people, so finally what I need to do is sum up those ratings, get their average and group them by poem id. Cool, I achieve that almost, but what I am missing is one column where I need to indicate what rate belongs to which post, without which, of course I provide no useful info.
I have this:
on styles.poems_id = poems.id
GROUP BY poems.id )
You can first find the
AVG of each
Poems using a subquery and then do a
Rates to get the description:
SELECT t.id, t.AvgStyle, r.RateDesc FROM ( SELECT p.id, ROUND(AVG(s.Value)) AS AvgStyle FROM Poems p INNER JOIN Styles s ON s.poems_id = p.id GROUP BY p.id ) t INNER JOIN Rates r ON r.digit = t.AvgStyle