Arminius Arminius - 6 months ago 19
SQL Question

SQL SUBQUERY to rate poems according to style, somewhat tricky?

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:

select ratedesc
from rates
where rates.digit
IN (SELECT
ROUND(AVG(styles.value))
FROM styles
join poems
on styles.poems_id = poems.id
GROUP BY poems.id )


which gets me a display like this:

Good
Standard
Excellent
Poor


so each of those adjectives is actually the avg of all the ratings for each poem expressed in a verbose and verbal way instead of just saying 5 or 8.
but, of course, I am missing to tell which poem do they refer to

I should be adding the poem_id, to the first select, but that requires changing completely the structure of the query, and this is what I am not achieving

Answer

You can first find the AVG of each Poems using a subquery and then do a JOIN on 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