CODEWITHSUNDEEP
mysqlsqlgroup-byaggregate
Emil Aspman
Emil Aspman

Reputation: 1016

mysql check if value exist in group

I have a table called votes with columns id, image_id, user_id and time.

How can I add a bool in the result for if a specific user_id is one of the votes for each image_id?

SELECT image_id,
count(image_id) as vote_count
FROM votes
GROUP BY image_id 
ORDER BY vote_count DESC

I have tried if(user_id = 18, 1, 0) as have_voted but it does not work for each image_id.

Upvotes: 7

Views: 10288

Answers (1)

Wookai
Wookai

Reputation: 21723

What about summing all the rows where the user_id is the one you want, and checking if this sum is greater than 0?

SELECT image_id,
count(image_id) as vote_count,
SUM(IF(user_id = 18,1,0)) > 0 AS hasUser18
FROM votes
GROUP BY image_id 
ORDER BY vote_count DESC

Upvotes: 16

Related Questions