CODEWITHSUNDEEP
mysqlsql
KennethC
KennethC

Reputation: 756

Retrieving the TOP 10 data with arithmetic operation

I want to retrieve the name of the user with the top 10 most voted post. the formula in calculating the votes should be upvotes-downvotes. I also need to count the votes of that post.

Here's my table user_info

id       lname        fname
1        abc1         abc1
2        abc2         abc2
3        abc3         abc3
4        abc4         abc4
5        abc5         abc5
6        abc6         abc6
7        abc7         abc7

table post

post_id         post_message        user_id
1               sup                 1
2               good morning        2
3               hello               5
4               test                3
5               sample              4

table vote

vote_id      user_id       post_id              vote_type
1            1             1                    upvote
2            2             1                    upvote 
3            1             2                    downvote
4            1             2                    upvote
5            2             2                    downvote
6            3             1                    upvote
7            3             4                    upvote
8            4             4                    downvote

here's the query that I tried.

SELECT post_id, fname,lname,COUNT(CASE WHEN vote_type = 'upvote' then 1 ELSE NULL END) as "upvotes", COUNT(CASE WHEN vote_type = 'downvote' then 1 ELSE NULL END) as "downvotes"
FROM user_info 

LEFT JOIN post ON 
post.user_id = user_info.id

LEFT JOIN vote ON
post.user_id = user_info.id 

ORDER BY 'upvotes'- 'downvotes' // is this possible?
LIMIT 10

My desired output is something like this.

post_id      lname        fname     vote
1            abc1         abc1      3       // 3 upvotes
4            abc3         abc3      0       // 1upvote - 1 downvote
3            abc5         abc5      0       // no vote
5            abc4         abc4      0       // no vote
2            abc2         abc2     -1       // 1upvote - 2 downvotes

I don't know where I should put that column "vote" but it should be there in my query.

I created an sql fiddle for this

Upvotes: 2

Views: 48

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Fiddle

select p.post_id,u.fname,u.lname,
case when sum(upvotes)+sum(downvotes) is null then 0
else sum(upvotes)+sum(downvotes) end as vote
 from post p left join
 (
 SELECT post_id,
 CASE WHEN vote_type = 'upvote' then 1 ELSE 0 END as "upvotes", 
 CASE WHEN vote_type = 'downvote' then -1 ELSE 0 END as "downvotes"
 FROM vote 
 ) t
 on p.post_id = t.post_id
 left join user_info u on p.user_id = u.id
 group by p.post_id
 order by vote desc, sum(upvotes) desc, sum(downvotes) desc
 limit 10;

Upvotes: 1

Related Questions