Rounding to the nearest 0.5

Yesterday I encountered a fun little problem where I had to round to the nearest 0.5. Admittedly, I solved the problem in C#, but the solution is the same no matter what language you are in.

So, here’s our starting data set:

IF OBJECT_ID('tempdb..#ratings') IS NOT NULL 
  DROP TABLE #ratings ;

CREATE TABLE #ratings ( rating INT ) ;

INSERT  INTO #ratings
        SELECT  1
        UNION ALL
        SELECT  3
        UNION ALL
        SELECT  2
        UNION ALL
        SELECT  7
        UNION ALL
        SELECT  9
        UNION ALL
        SELECT  8
        UNION ALL
        SELECT  3 ;

And, of course, the average is really easy to get as well:

SELECT  AVG(CAST(rating AS DECIMAL)) -- Use decimal so we don't lose precision
FROM    #ratings ;

But that average really doesn’t do anybody any good since it’s nice and precise. How are we going to get to this number? Well, funnily enough if we multiple our average by 2 and round it to the nearest whole number and divide it by 2 (still with me?) we’ll end up with a number that’s rounded to the nearest 0.5.

SELECT  ROUND(AVG(CAST(rating AS DECIMAL)) * 2, 0) / 2
FROM    #ratings ;

It’s also possible to change your rounding precision simply by change the number you multiply and divide by… Want everything in thirds? Just use 3 instead of 2. Magic! You can use this in your rating algorithms to give you half or quarter stars or whatever the heck you want. Happy rounding!