Q
Manage Learn to apply best practices and optimize your operations.

How to use an SQL CASE expression

Read an example of an SQL case expression from our SQL expert Rudy Limeback.

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?

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 last published in April 2009

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close