MySQL percentage (a bit complex?)

Question

There are a few of these around, but I am either slow tonight or I am trying to do something not possible. I have this setup

`id` int(11) NOT NULL AUTO_INCREMENT,
`score` float NOT NULL,
`userId` int(11) NOT NULL
`movieId` int(11) NOT NULL

Taking that into account, I want to get a percentage of all the experiences from all the users in a given movie, how will I be able to do this since users can rate them as "good" with any value above 0 and as "bad" with any value below 0? Let's say a given movie has 4 experiences, where 3 are "2" and 1 is "-2" I would like to return the "porcentage" of "likeness" of that movie.

Am I crazy?

Answer 1

Use:

  SELECT x.movieid,
         x.score,
         COUNT(x.userid) / y.score_count  AS percentage
    FROM YOUR_TABLE x
    JOIN (SELECT t.movieid,
                 COUNT(t.score) AS score_count
            FROM YOUR_TABLE t
        GROUP BY t.movieid) y ON y.movieid = x.movieid
GROUP BY x.movieid, x.score

Answer 2

This should help get you started. I've used TSQL from SQL Server, but if the syntax isn't the same (it appears CAST should be the same in MySQL), just give it a tweak here and there will get you going in MySQL.

SELECT Score,
       (NumWithScore/(SELECT CAST(COUNT(userid) as decimal(10,2)) From Movies where MovieID= 7  ) * 100 
       )AS PercentageVotes
FROM 
(
     SELECT Score,           
            COUNT(*) AS NumWithScore          
     FROM  Movies AS M
     WHERE  MovieID = 7
     GROUP BY Score
) AS S

The data I used was:

• 2 items with value 2
• 1 item with value -2

Results in:

Score | PercentageVotes
------+----------------
2.00  |   66.6666666
-2.0  |   33.3333333

Full code up on PasteBin.