CODEWITHSUNDEEP
sqlmysqlaggregate-functions
raulriera
raulriera

Reputation: 724

MySQL percentage (a bit complex?)

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?

Upvotes: 0

Views: 576

Answers (2)

OMG Ponies
OMG Ponies

Reputation: 332591

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

Upvotes: 0

p.campbell
p.campbell

Reputation: 100587

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.

Upvotes: 1

Related Questions