How to use an SQL CASE expression

I'm hoping you can help me with a MySQL rounding issue where I have hit the wall. I'm attempting to take an integer column, 'x', from table 'y' and achieve the following:

If x = 0, then x = 0
If x <= 30, then x = 30
If x > 30, then round x to nearest multiple of 6.

I can do the following to round to nearest multiple of 6, but this does not help me for values <= 30:

select ceiling(x/6)*6 from y;

Can you help?

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

The solution here is to use a CASE expression. I think you might be pleasantly surprised how closely it resembles your original specs:

SELECT CASE WHEN x = 0   THEN 0
            WHEN x <= 30 THEN 30
            WHEN x > 30  THEN CEILING(x/6)*6 
        END AS result
  FROM y

Two comments. First, CASE expressions are evaluated linearly. That is, each condition is evaluated in turn, starting at the top, and the first condition that evaluates to TRUE will determine the resulting value, as given by its THEN expression.

Secondly, your specs did not mention what you wanted if x should happen to be negative. Thus, in the above CASE expression, negative x values would all be assigned the resulting value of 30. Can you see why?

This was first published in April 2009

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top