MySQL - How to run a SELECT query that orders results on an account - for example, upvotes - downvotes

Question

I have two tables - an article table and a voting table. Users can vote or vote for articles of their choice (similar to Reddit). Fields that I have in the voting table:

• article_id
• user_id
• Voting

The value of the voting field can be 0 or 1 ... (0 if they voted for the article, 1 if they vote).

I am trying to execute a SELECT query that returns all articles with the highest score. That is, upvotes minus downvotes. However, I completely lost how this will be done. I can return all the articles that have the most revolutions, for example:

-- article table is called "article"
-- vote table is called "user_article_vote"

SELECT article.title, article.summary, COUNT( user_article_vote.vote ) AS votes
FROM article
INNER JOIN user_article_vote ON article.article_id = user_article_vote.article_id
WHERE user_article_vote.vote = '1'
ORDER BY votes

, (upvotes - downvotes)?

.

Answer 1

-, downvotes -1 0. , .

SELECT :

SELECT article.title, 
       article.summary, 
       SUM( CASE user_article_vote.vote WHEN 0 THEN -1 ELSE 1 END ) AS balance
FROM article
INNER JOIN user_article_vote ON article.article_id = user_article_vote.article_id
ORDER BY balance DESC    

MySQL, , GROUP BY article.title, article.summary . .