peppy peppy - 3 months ago 8
MySQL Question

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.

Answer

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
Comments