How to select all this data without repeating subqueries?

Question

Say I have a table called 'users', a table called 'posts' and a table called 'ratings', that stores the rating of each user to each post. If I wanted to select the posts, together with their thumbs up, thumbs down and 'average' ratings (rounded to the second decimal), I would do:

SELECT *,
    (SELECT COUNT(*) FROM ratings WHERE thumb='up' AND post_id = posts.id) AS thumbs_up,
    (SELECT COUNT(*) FROM ratings WHERE thumb='down' AND post_id = posts.id) AS thumbs_down,
    ROUND((SELECT COUNT(*) FROM ratings WHERE thumb='up' AND post_id = posts.id) / (SELECT COUNT(*) FROM ratings WHERE post_id = posts.id), 2) AS average_rating
FROM posts;

But is there a way to obtain this same data without repeating the subqueries? I mean, ideally I would like to do:

SELECT *,
    (SELECT COUNT(*) FROM ratings WHERE thumb='up' AND post_id = posts.id) AS thumbs_up,
    (SELECT COUNT(*) FROM ratings WHERE thumb='down' AND post_id = posts.id) AS thumbs_down,
    ROUND(thumbs_up / (thumbs_up + thumbs_down), 2) AS average_rating
FROM posts;

But MySQL does not allow this. What is next best thing? Or is there an even better way, by using JOINs or UNIONs?

Answer 1

SELECT
    *,
    ROUND(thumbs_up / (thumbs_up + thumbs_down), 2) AS average_rating
FROM (
    SELECT
        YourColumns...
        SUM(CASE WHEN r.thumb = 'up' THEN 1 END) AS thumbs_up,
        SUM(CASE WHEN r.thumb = 'down' THEN 1 END) AS thumbs_down
    FROM
        posts p LEFT JOIN
        ratings r ON r.post_id = p.id
    GROUP BY YoursColumns
) sub

Answer 2

You could simply use variables inside a stored procedure. Code is concept and may need adjustment to fit mysql.

SET @thumbsup = 0;
SET @thumbsdown = 0;

SELECT @thumbsup = COUNT( * ) FROM ratings WHERE thumb = 'up' AND post_id = @someid;
SELECT @thumbsdown = COUNT( * ) FROM ratings WHERE thumb = 'down' AND post_id = @someid;

RETURN @thumbsup, @thumbsdown, ROUND( @thumbsup / ( thumbsup + thumbsdown ), 2 );

Answer 3

Why not having in your "posts" table a column "ups" and "downs" where are summarized / counted the ups and downs ? you would end with something like this :

SELECT ups, downs, ROUND(ups / (ups + downs), 2) AS average_rating FROM posts where id = anId;