Mysql case statement with numeric values

Question

I have table with

+------------+-------+-------+
| tran_value | date  | name  |
+------------+-------+-------+
|         10 | 22may | mark  |
|         15 | 23may | mark  |
|         16 | 24may | mark  |
|        300 | 19jun | john  |
|        400 | 20jun | john  |
|        500 | 21jun | john  |
|        600 | 22jun | john  |
|        150 | 12dec | chris |
|        160 | 13dec | chris |
|        170 | 14dec | chris |
|        180 | 15dec | chris |
+------------+-------+-------+

and i tried to add outlier in a new column but it is throwing error

select date,tran_value,
case name
when name ="mark" and tran_value <=15 then new_trn =NULL
  else tran_value
when name ="john" and tran_value <400 then new_trn =NULL
  else tran_value
when name ="chris" and tran_value <170 then new_trn =NULL
  else tran_value

ii.e

+------------+-------+-------+
| tran_value | date  | name  |
+------------+-------+-------+
| NULL       | 22may | mark  |
| NULL       | 23may | mark  |
| 16         | 24may | mark  |
| NULL       | 19jun | john  |
| NULL       | 20jun | john  |
| 500        | 21jun | john  |
| 600        | 22jun | john  |
| NULL       | 12dec | chris |
| NULL       | 13dec | chris |
| 170        | 14dec | chris |
| 180        | 15dec | chris |
+------------+-------+-------+

any modifications in code.... thanks in advance

Ankit Agrawal · Accepted Answer · 2016-02-16 09:17:22Z

2

SELECT date,tran_value,CASE 
        WHEN name ="mark" and tran_value <=15 THEN new_trn =NULL         
        WHEN name ="john" and tran_value <400 THEN new_trn =NULL         
        WHEN name ="chris" and tran_value <170 THEN new_trn =NULL
        ELSE tran_value
END AS new_column;

edited Feb 16, 2016 at 9:17

answered Feb 16, 2016 at 6:50

Ankit Agrawal

2,4541 gold badge17 silver badges28 bronze badges

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

2 Comments

fancyPants Over a year ago

Remove name after CASE or alternatively the name = after the WHEN.

su919 Over a year ago

Thanks a lot @user ,@fancypants

Collectives™ on Stack Overflow

Mysql case statement with numeric values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related