How can i count votes for a post-CodePudding

I have two tables, with posts and with votes for this posts: First table contains posts data, including ID Second one contains ID of post and value (Positive vote or negative, 1/0).

So for example in first table i have a post, and in second i have 3 votes: 2 positive, and 1 negative.

I founded how to find count of all votes for a post:

 SELECT posts.id, COUNT(posts.id) AS votes_count
    FROM posts LEFT JOIN votes 
    ON posts.id = votes.post_id
    GROUP BY posts.id
    ORDER BY votes_count

And i founded how to count seperate positive or negative votes for a post: (Here is positive, because value is 1. Negative - 0)

SELECT posts.id, COUNT(posts.id) AS votes_count
    FROM posts LEFT JOIN votes 
    ON posts.id = votes.post_id WHERE value = 1
    GROUP BY posts.id
    ORDER BY votes_count

But how can i found the rating of a post? So if ill have 2 positive and 1 negative votes: 2 - 1 = 1

UPD: tables structure:

Post
|------------|
|     ID     |
|------------|
|     5      |
|------------|

Votes:
|--------------------|
| POST ID |  VALUE   |
|--------------------|
|    5    |    0     | /// NEGATIVE
|--------------------| 
|    5    |    1     | /// POSITIVE
|--------------------|
|    5    |    1     | /// POSITIVE
|--------------------|

What i want to get in end:

|--------------------|
| POST ID |  RATING  |
|--------------------|
|    5    |    1     | // BECAUSE 2 POSITIVE AND 1 NEGATIVE, SO 2 - 1 = 1
|--------------------|

CodePudding user response：

SELECT post.id, 
SUM(CASE WHEN votes.`value`=0 THEN -1 ELSE 1 END) AS votes_count
FROM post LEFT JOIN votes 
ON post.id = votes.post_id 
GROUP BY post.id
ORDER BY votes_count

Here is your answer to your current question But I don't see you mentioning the case if a post has all negative votes

CodePudding user response：

You can use IF() as well

SELECT post.id, SUM(IF(votes.post_val=0, -1, 1)) AS rating
FROM post LEFT JOIN votes 
    ON post.id = votes.post_id 
GROUP BY post.id
ORDER BY rating

You may refer to this example