Arminius Arminius - 2 years ago 81
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
FROM styles
join poems
on styles.poems_id =

which gets me a display like this:


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 Source

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.AvgStyle, r.RateDesc
    SELECT, ROUND(AVG(s.Value)) AS AvgStyle
    FROM Poems p
    INNER JOIN Styles s
        ON s.poems_id =
) t
    ON r.digit = t.AvgStyle
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download