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!