Rounding T-SQL to 0.0, 0.5, or 1.0

Question

I have an interesting problem where I need to round the result of the AVG () function in a certain way for use in the ORDER BY clause.

They are intended for use in recipe ratings.

Examples of rounding formulas:

1.2 -> 1 1.4 -> 1 1.5 -> 1.5 1.6 -> 2 1.9 - >2 

I am asking for a list of recipes, and they had to be ordered so that a recipe with one 5-star rating was not ordered above a recipe with 100 ratings, which average 4.9. The second part of the order by points is the calculation of ratings.

If this requires a custom function, I'm not quite sure how to do this.

Answer 1

 ORDER BY CASE WHEN (Num % 1) = .5 THEN Num ELSE ROUND(Num,0) END 

Answer 2

If I understand correctly, do you have stars and half stars? I suggest ORDER BY ROUND(value*2, 0)/2 , so it rounds to the nearest step 0.5 (semi-star).