MySQL CASE for value range doesn't work but nested IF's do?

Question

I'm probably missing something that is really, really simple, but I can't for the life of me figure out what it is that I'm not doing correctly...

I have this query which is used to pull out hours people have completed in volunteering and then assign them an award based on the amount of hours submitted. Not difficult...

The nested IF solution is horrible and was only a fallback to see if it was just the CASE that was messing up. Turns out, the janky nested IF solution works perfectly, where as my CASE solution is still broken.

The query is only run once annually to pull off final results, so performance isn't really a problem (the nested IF query currently has an execution time of 0.0095 seconds / 700 rows which is perfectly adequate), it's more of the fact that it is thoroughly annoying my that it's not working and want to understand why for future reference.

For reference the hour values are stored as DECIMAL(8,2), subsequently the value of total_hours is also of the same type.

The output I'm looking for is:

| id | first_name | last_name  | total_hours | award    |
|----|------------|------------|-------------|----------|
| 1  | Bob        | Harrington | 0.50        | Silver   |
| 2  | Jim        | Halpert    | 800.00      | Platinum |
| 3  | Dwight     | Shrute     | 130.00      | Gold     |
| 4  | Michael    | Scott      | 5.00        | Bronze   |

The CASE statement results in all rows having the value of 'Less than 1 hour' for award, EXCEPT those where total_hours equals 1.00, in which the value of award equals 'Bronze'.

The nested IF statements result in the table being generated correctly, as per the example above.

Here is my current CASE query, that doesn't work:

SELECT
    m.id,
    m.first_name,
    m.last_name,
    total_hours,
    CASE total_hours
        WHEN total_hours >= 1 <= 50 THEN
            'Bronze'
        WHEN total_hours >= 51 <= 125 THEN
            'Silver'
        WHEN total_hours >= 126 <= 249 THEN
            'Gold'
        WHEN total_hours >= 250 THEN
            'Platinum'
        ELSE
            'Less than 1 hour'
    END AS award
FROM (
    SELECT member_id, sum(hours) total_hours
    FROM volunteering_hours
    WHERE authorise_date > 0 AND validate_date > 0 AND delete_date = 0
    GROUP BY member_id
) hour_query
LEFT JOIN members m ON m.id = member_id
ORDER BY total_hours DESC

What I've tried so far:

Placing the raw comparison numeric values in quotes.
Giving the comparison numeric values decimal places.
Trying the CASE statement with only one comparison, just as a test, that being; WHEN total_hours > 1 THEN 'GT 1' ELSE 'LT 1' END award, all columns where still coming up as LT 1 after running the query - meaning it's failed.
Grouping the CASE statement
Changing the syntax of each range comparison to total_hours >= 1 && total_hours <= 50, etc.. and it still yielded the same failed result

My current nested IF solution which looks horrible, but at least is working, is:

SELECT
    m.id,
    m.first_name,
    m.last_name,
    total_hours,
    IF(total_hours >= 1 && total_hours <= 50, 'Bronze',
        IF(total_hours >= 51 && total_hours <= 125, 'Silver',
            IF(total_hours >= 126 && total_hours <= 249, 'Gold',
                IF(total_hours >= 250, 'Platinum', 'Less than 1 hour')
            )
        )
    ) award
FROM (
    SELECT member_id, sum(hours) total_hours
    FROM volunteering_hours
    WHERE authorise_date > 0 AND validate_date > 0 AND delete_date = 0
    GROUP BY member_id
) hour_query
LEFT JOIN members m ON m.id = member_id
ORDER BY total_hours DESC

Can someone please shower me in some knowledge as to why the CASE isn't working?

Thanks in advance. :)

jpw · Accepted Answer · 2015-04-30T11:07:17.077

3

You were close but had some syntax errors. Do this instead:

CASE 
    WHEN total_hours >= 1 AND total_hours <= 50 THEN
        'Bronze'
    WHEN total_hours >= 51 AND total_hours <= 125 THEN
        'Silver'
    WHEN total_hours >= 126 AND total_hours <= 249 THEN
        'Gold'
    WHEN total_hours >= 250 THEN
        'Platinum'
    ELSE
        'Less than 1 hour'
END AS award

Sample simplified SQL Fiddle

edited Apr 30 '15 at 11:07

answered Apr 30 '15 at 10:56

jpw

44,361
6
66
86

Thanks! This is perfect, although @dognose provided an explaination as to why, which helped. So I'm unsure as to which one to mark as the answer, not to discredit you at all, it really did help! For that I am very thankful. :) – Othyn Apr 30 '15 at 13:49
@Othyn Happy to help! Choose the answer that you felt best provided a solution. – jpw Apr 30 '15 at 16:17

dognose · Answer 2 · 2015-04-30T11:07:32.487

2

You mixed the (different) case syntax.

If you are using case xx, then your WHEN should not contain xx again:

case xx
  when 1 then statement
  when 2 then statement

If you are using just case then you can need to provide the variables to compare:

case
  when xx=1 then statement
  when xx=2 then statement

See example here: http://sqlfiddle.com/#!9/d8348/6

compared with programming languages the first one equals

switch(variable){
   case 1: statement; break;
   case 2: statement; break;
}

while the second one is

if (variable==1){
   statement;
else if (variable==2){
   statement;
}

edited Apr 30 '15 at 11:07

answered Apr 30 '15 at 11:00

dognose

20,360
9
61
107

Thank you! :) Although I'm unsure whether to mark you or @jpw 's answer as the answer, as they were both as helpful as each other! :) – Othyn Apr 30 '15 at 14:02

Ryan-Neal Mes · Answer 3 · 2015-04-30T10:59:15.370

Try the following.

SELECT
    m.id,
    m.first_name,
    m.last_name,
    total_hours,
    CASE WHEN total_hours between 1 and 50 THEN
            'Bronze'
        WHEN total_hours between 51 and 125 THEN
            'Silver'
        WHEN total_hours between 126 and 249 THEN
            'Gold'
        WHEN total_hours between 250 THEN
            'Platinum'
        ELSE
            'Less than 1 hour'
    END AS award
FROM (
    SELECT member_id, sum(hours) total_hours
    FROM volunteering_hours
    WHERE authorise_date > 0 AND validate_date > 0 AND delete_date = 0
    GROUP BY member_id
) hour_query
LEFT JOIN members m ON m.id = member_id
ORDER BY total_hours DESC

I am not sure if you can use between in your cases statement like that, but I think you can. If you can't then you just need to break it up correctly like total_hours >=1 and total_hours <=50 in your cases statement.

As for your query, I would recommend restructuring it something like

select
    m.id,
    m.first_name,
    m.last_name,
    sum(vh.hours) total_hours,
    case when sum(vh.hours) between 1 and 50 then
            'bronze'
        when sum(vh.hours) between 51 and 125 then
            'silver'
        when sum(vh.hours) between 126 and 249 then
            'gold'
        when sum(vh.hours) between 250 then
            'platinum'
        else
            'less than 1 hour'
    end as award
from
    volunteering_hours vh

    left join members m on 
        m.id = member_id
where 
    authorise_date > 0 and 
    validate_date > 0 and 
    delete_date = 0
group by 
    vh.member_id,
    m.id,
    m.first_name,
    m.last_name
order by 
    total_hours desc

Nested tables are not great and you should avoid using them if possible. A bit cleaner and I think the grouping still works fine I think.

MySQL CASE for value range doesn't work but nested IF's do?

3 Answers3