Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

T-SQL Rounding to 0.0, 0.5, or 1.0

I have an interesting issue where I need to round the result of an AVG() function in a specific manner for use in the ORDER BY clause.

These are for use in recipe ratings.

Examples of the rounding formula:

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

I am querying for a list of recipes, and they needed to be ordered so that a recipe with one 5-star rating is not ordered a above a recipe with 100 ratings that average 4.9. The second part of the order by clause is the count of ratings.

If this requires a user defined function, I'm not quite sure how to go about doing it.

like image 329
Danomite Avatar asked Feb 24 '23 14:02

Danomite


1 Answers

ORDER BY
    CASE WHEN (Num % 1) = .5
        THEN Num
        ELSE ROUND(Num,0)
    END
like image 148
The Scrum Meister Avatar answered Feb 26 '23 12:02

The Scrum Meister