SQL: Why does 'Case When' won't work in my code?

Question

Here is my ER Diagram

I want to categorize the stars attribute in business table into something like this:

• 2.0 - 3.0 stars = '2-3'
• 4.0 - 5.0 stars = '4-5'
• other = 'none'

I tried using this code:

select case b.stars
when (b.stars >= 2.0 and b.stars <=3.0) then '2-3'
when (b.stars >= 4.0) then '4-5'
else 'none'
end stars_group
from business b

But the case when doesn't work in this table, How to solve this?

Answer 1

You are confusing the two types of cases. You want the version with separate conditions:

select (case when (b.stars >= 2.0 and b.stars <= 3.0) then '2-3'
             when (b.stars >= 4.0) then '4-5'
             else 'none'
        end) as stars_group

If you are just using equality, you can use a simple case expression -- but the comparisons need to strict equality:

select (case trunc(b.stars)
             when 2 then 'Two'
             when 3 then 'Three'
             else 'none'
        end) as stars_group

With inequalities, you need a searched case where each where clause is evaluated to determine the first then that is returned. The searched case has no expression between case and where.