Select percentage of rows with different values

Question

On a website I run, I let users rate the individual posts (3, 2, 1). I use the following SQL (in MySQL) to get the percentage of votes of each value:

SELECT vote, COUNT(*) * t.factor AS pct
FROM ratings
JOIN (
    SELECT 100 / COUNT(*) AS factor
    FROM ratings
) AS t
GROUP BY vote
LIMIT 0, 30;

That works fine for calculating the percentage of rows for the whole table. Now, I need the percentages just for a particular post, identified in the table by the column "id". How might I do that?

Answer 1

Assuming that the vote column holds the value of the vote i.e. (3,2,1), then you would need the following query for tabulating the percentages per vote:

SELECT r.id, r.vote, SUM(r.vote)/t.totalVotes AS percentOfVotes
FROM ratings r
JOIN (SELECT id, COUNT(id) AS totalVotes
      FROM ratings
      GROUP BY id ) AS t ON t.id = r.id
WHERE r.id = @id -- id of post you want
GROUP BY r.id, r.vote

Answer 2

The question seems a little misleading...but here is what I'd imagine it would look like.

SELECT vote, COUNT(*) * t.factor AS pct
FROM ratings
JOIN (
SELECT 100 / COUNT(*) AS factor
FROM ratings
where id = id of the post
) AS t
where id = id of the post
GROUP BY vote

Answer 3

One way that works, but I've been advised isn't the most efficient in the world is the following:

SELECT vote, COUNT(*) * t.factor AS pct
FROM ratings
JOIN (
    SELECT 100 / COUNT(*) AS factor
    FROM ratings
    WHERE id = 36
) AS t
WHERE id = 36
GROUP BY vote
LIMIT 0, 30;

I had tried adding a where clause to the inner and the outer selects, but not to both! Makes sense, of course.