CODEWITHSUNDEEP
mysql
peppy
peppy

Reputation: 195

MySQL: If IN() statement within Select? How do I query this?

How do I write the query like:

SELECT userid, 
 (IF field IN(1,2,3,900,658,4,5,6,9,10,11,12,13,10025,14,15,16,17,18,19) score = score +20, ELSE IF field IN(23,45,67,777,53,54) score = score + 40,
  ELSE IF field IN(120,332,563,334,634,423,333) score = score + 60) AS score 
 WHERE field IN(1,2,3,900,658,4,5,6,9,10,11,12,13,10025,14,15,16,17,18,19,23,45,67,777,53,54,120,332,563,334,634,423,333) 
GROUP BY userid ORDER BY score DESC

I'm trying to find all users who have a field set to one of the given numbers. Certain numbers have different scores (20 points, 40 points and 60 points), so I need to add up the scores to get a total score for each userid and then rank users by the total score.

Upvotes: 0

Views: 29

Answers (2)

trincot
trincot

Reputation: 350242

You might be looking for this:

SELECT   userid, 
         sum(
             CASE
                WHEN field IN (1,2,3,4,5,6,9,10,11,12,13,14,15,16,17,18,19) THEN 20 
                WHEN field IN (23,45,67,53,54) THEN 40
                ELSE 60 
             END) AS score
FROM     mytable 
WHERE    field IN (1,2,3,4,5,6,9,10,11,12,13,14,15,16,17,18,19,23,45,67,53,54,120,
                   332,563,334,634,423,333)
GROUP BY userid
ORDER BY score DESC

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

You missed th from Table and you can do with case 

select UserId,
     case 
       when field < 20 then  Score + 20
       when field IN(23,45,67,53,54) then  Score + 40
       else  score + 60
      end as Score
FROM mytable
where field IN(1,2,3,4,5,6,9,10,11,12,13,14,15,16,17,18,19,23,45,67,53,54,120,332,563,334,634,423,333) 
group by UserId
order by Score desc

Upvotes: 0

Related Questions