how to use if conditions in mysql

Question

I am trying to get some results through query that would be in range from -50 to +50. now according to the range I need to have value in column comment.

The conditions are

    >=-50 and < -30 = very negative
    =< -30 and < -10 = negative
    =< -10 and < +10 = neutral
    =< +10 and < +30 = positive
    =< +30 and < +50 = very positive

the output i am trying to get is:

range  comment
-45     very negative
-20     negative

I can just get the value how can i set comment according to it.

dmitryro · Accepted Answer · 2016-07-04 03:11:05Z

1

Your query is:

SELECT * FROM your_table WHERE range BETWEEN -50 AND 50;

answered Jul 4, 2016 at 3:11

dmitryro

3,5162 gold badges23 silver badges30 bronze badges

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

Comments

KaeL · Accepted Answer · 2016-07-04 03:12:28Z

1

You can use CASE syntax.

CASE 
   WHEN range BETWEEN -50 AND -31 THEN 'very negative'
   WHEN range BETWEEN -30 AND -11 THEN 'negative'
   ...
   ELSE 'very positive'
END CASE

answered Jul 4, 2016 at 3:12

KaeL

3,6612 gold badges31 silver badges59 bronze badges

Comments

Tim Biegeleisen · Accepted Answer · 2016-07-04 03:11:57Z

0

This sounds like you want to use a CASE WHEN expression:

SELECT range,
    CASE WHEN range BETWEEN -50 AND -30 THEN 'very negative'
         WHEN range BETWEEN -30 AND -10 THEN 'negative'
         WHEN range BETWEEN -10 AND 10  THEN 'neutral'
         WHEN range BETWEEN 10  AND 30 THEN 'positive'
         WHEN range BETWEEN 30  AND 50 THEN 'very positive'
    END AS comment
FROM yourTable

answered Jul 4, 2016 at 3:11

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

2 Comments

Bloomberg Over a year ago

hey Tim thanks for answer my query is select user_id,TRUNCATE(avg(perception_score)*100/48,2) avg_wbs, CASE WHEN perception_score BETWEEN -50 AND -30 THEN 'very negative' WHEN perception_score BETWEEN -30 AND -10 THEN 'negative' WHEN perception_score BETWEEN -10 AND 10 THEN 'neutral' WHEN perception_score BETWEEN 10 AND 30 THEN 'positive' WHEN perception_score BETWEEN 30 AND 51 THEN 'very positive' END AS comments from notes group by user_id,comments;

Bloomberg Over a year ago

it gives result. 282 50.00 positive 285 2.08 neutral 285 50.00 positive 287 9.63 neutral 287 40.62 positive 288 -28.81 negative its never giving very positive and very negative could you tell whats wrong?

Collectives™ on Stack Overflow

how to use if conditions in mysql

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related