MySQL IF in SELECT clause?

Question

I've been working with mysql for a while but I'd never come across a problem where I had to use a condition up to now. Basically I need to do this (the best way for me to explain it is to write it as if mysql supported php like conditions):

SELECT `Gender`=='male' ? '0.5' : '1' AS Col1 FROM Users

Basically I want to have a 0.5 AS Col1 for all 'males' and a 1 for all 'females'.

Eg:

UID Gender
1   male
2   female
3   male

Should return:

UID Col1
1   0.5
2   1
3   0.5

If you are wondering about the implementation. I want to recommend users "who to follow" based on their common habits. (0.5 to men means that they follow twice the number of women as men).

How can this be done?

I hope that does not mean male are only half worth as female :) — juergen d
– juergen d, Commented Aug 17, 2012 at 6:33

juergen d · Accepted Answer · 2012-08-17 06:31:44Z

5

You can use

SELECT if(`Gender`='male','0.5','1') AS Col1
FROM Users

or

SELECT case when `Gender`='male'
            then '0.5'
            else '1'
       end AS Col1
FROM Users

answered Aug 17, 2012 at 6:31

juergen d

205k40 gold badges304 silver badges377 bronze badges

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

Comments

sel · Accepted Answer · 2012-08-17 06:32:15Z

3

select uid,
    case gender
        when 'male' then '0.5'
        when 'female' then '1'
    end as genderindigit
from
    users

answered Aug 17, 2012 at 6:32

sel

4,9421 gold badge18 silver badges23 bronze badges

Comments

John Woo · Accepted Answer · 2012-08-17 06:34:52Z

2

try

SELECT IF(`Gender` = 'male', '0.5', '1') AS Col1
FROM Users

answered Aug 17, 2012 at 6:34

John Woo

265k70 gold badges509 silver badges500 bronze badges

Collectives™ on Stack Overflow

MySQL IF in SELECT clause?

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related