mardi 8 mars 2016

SQL CASE conditions always returning ELSE value

I have a mini database (mymoney) which has a column called total_amount. Now I would like to get this column and ad an extra column GRP2 (with values low, medium or high) based on the row value in the total_amount column. So I have this:

total_amount
5000
27000
36000
50000

And I would like to the this:

total_amount    GRP2
5000            low
27000           medium
36000           medium
50000           high

I tried with the following SQL query:

SELECT mymoney.total_amount,
    CASE mymoney.total_amount
        WHEN mymoney.total_amount <= 30000 THEN 'low'
        WHEN mymoney.total_amount >= 31000 AND mymoney.total_amount <= 40000 THEN 'medium'
        ELSE 'high'
    END AS GRP2
FROM gates_money

But in GRP2 I get high everywhere, even though there are clearly numbers in total_amount which should be marked low and medium. (btw: the data types for the values in total_amount are all integers).

total_amount    GRP2
5000            high
27000           high
36000           high
50000           high

Can anyone please tell me what I'm doing wrong?

Aucun commentaire:

Enregistrer un commentaire