Menu
I'm not sure if this has been asked before, but how do I get the average rounded to the closest integer in T-SQL?
578
The ROUND() function in MySQL is used to round a number to a specified number of decimal places. If no specified number of decimal places is provided for round off, it rounds off the number to the nearest integer.
SQL AVG() with ROUND(), group byThe SQL ROUND() is used to round the value up to a specific decimal places. The GROUP BY clause with aggregate function makes the result within a group.
Notice that we need a complex expression involving CEILING, in order to get the "rounded up to next 1000" number that you wanted. The "trick" (if you want to call it that) is to divide by 1000.0, which forces a decimal result, before applying the CEILING.
SQL CAST() inside AVG() for decimal valueThe SQL AVG() function returns the average value with default decimal places. The CAST() is used to increase or decrease the decimal places of a value. The CAST() function is much better at preserving the decimal places when converting decimal and numeric data types.
This should do it. You might need a GROUP BY on the End depending on what you are looking for the average of.
SELECT CONVERT(int,ROUND(AVG(ColumnName),0))
FROM
TableName
EDIT: This question is more interesting than I first thought.
If we set up a dummy table like so...
WITH CTE
AS
(
SELECT 3 AS Rating
UNION SELECT 4
UNION SELECT 7
)
SELECT AVG(Rating)
FROM
CTE
We get an integer average of 4
However if we do this
WITH CTE
AS
(
SELECT 3.0 AS Rating
UNION SELECT 4.0
UNION SELECT 7.0
)
SELECT AVG(Rating)
FROM
CTE
We get a decimal average of 4.666..etc
So it looks like the way to go is
WITH CTE
AS
(
SELECT 3 AS Rating
UNION SELECT 4
UNION SELECT 7
)
SELECT CONVERT(int,ROUND(AVG(CONVERT(decimal,Rating)),0))
FROM CTE
Which will return an integer value of 5 which is what you are looking for.
111
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
