Mysql query substract issue

Question

To begin with I have two tables:

feeds:(id,content_id,author) and feeds_ratings:(id,feed_id (FK to feeds(id)),user_id,rating)

What I want to do is get the total rating difference for a specific author by a certain user.

To explain a bit more, let's say we have three rows in the feeds table, (1,3245,test),(2,3215,test),(3,3122,test) and tree rows on the feeds_ratings table, (1,1,12,like), (1,2,12,like),(1,3,12,dislike)

The input will be the user_id and the author and I want the output to be the difference between the total dislikes and likes by the input user, for the specific input author. (In this example it will be 1 because of the two likes and the one dislike.

How can that be implemented in a mysql query? I tried searching and some code of my own but I can't make it work, so any help is appreciated!

Why not provide a sqlfiddle? (This should be a standard requirement.) — Strawberry
– Strawberry, Commented May 27, 2013 at 15:22

sgeddes · Accepted Answer · 2013-05-27 15:25:12Z

2

Something like this will work using SUM with CASE -- add 1 for likes and -1 for dislikes:

SELECT SUM(CASE WHEN fr.rating = 'like' THEN 1 ELSE -1 END) TotalLikes
FROM Feeds F
  INNER JOIN Feeds_Ratings FR ON F.id = FR.feed_id
WHERE F.author = 'test' 
  AND FR.user_id = 12

SQL Fiddle Demo

Obviously replace author and user_id with the appropriate values -- these are just for your sample input.

answered May 27, 2013 at 15:25

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Systemfreak Over a year ago

Sweet lord that was fast! I couldn't figure out how to do the sum part, thanks a lot. I will mark it as accepted in a jiffy.

sgeddes Over a year ago

@Systemfreak -- np, glad I could help!

Collectives™ on Stack Overflow

Mysql query substract issue

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related