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 ;