Joining tables, if percentage is above certain value
- by CluelessGerman
My question is similar to this one:
Compare rows and get percentage
However, little different. I adapted my question to the other post.
I got 2 tables.
First table:
user_id | post_id
1 1
1 2
1 3
2 12
2 15
And second table:
post_id | rating
1 1
1 2
1 3
2 1
2 5
3 null
3 1
3 4
12 4
15 1
So now I would like to count the rating for each post, in the second table.
If the rating has more than, lets say, 50% positive ratings than I want to get the post_id and going it to the post_id from table one and add 1 to the user_id.
At the end it would return the user_id with the number of positive posts.
The result for above table would be:
user_id | helpfulPosts
1 2
2 1
The post with post_id 1 and 3 have positive rating, because more than 50% have ratings of 1-3. The post with id = 2 is not positive, because the rating is exactly 50%.
How would I achieve this?
For clarification:
It's a mysql rdbm and a positive post, is one where the number of rating_ids with 1, 2 and 3 are more than half of the overall rating. Basically the same thing, from the other thread I posted above.
