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?
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
SQL expert Rudy Limeback explains how to use string functions to make an SQL join using only a portion of a column value. Continue Reading