T-SQL平均四舍五入到最接近的整数

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?

我不确定之前是否有人问过这个问题，但是我如何将平均值四舍六入到T-SQL中最接近的整数呢?

Thanks

谢谢

7 个解决方案

#1

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

我们得到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

我们得到的小数平均值是4。666

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.

它会返回一个整数值5，这就是你要找的。

#2

If you are in SQL Server, just use round(avg(column * 1.0), 0).

如果您在SQL Server中，请使用round(avg(column * 1.0)， 0)。

The reason for * 1.0 is because sql server in some cases returns calculations using the same datatype of the values used in the calculation. So, if you calculate the average of 3, 4 and 4, the result is 3.66..., but the datatype of the result is integer, therefore the sql server will truncate 3.66... to 3, using * 1.0 implicit convert the input to a decimal.

* 1.0的原因是sql server在某些情况下使用计算中使用的值的相同数据类型返回计算。所以，如果你计算3、4和4的平均值，结果是3。66…，但是结果的数据类型为integer，因此sql服务器将截断3.66…对于3，使用* 1.0隐式将输入转换为小数。

Alternatively, you can convert or cast the values before the average calculation, like cast(column as decimal) instead of using the * 1.0 trick.

或者，您可以在平均计算之前转换或转换值，如cast(列为十进制)，而不是使用* 1.0技巧。

If your column it's not a integer column, you can remove the * 1.0.

如果列不是整数列，可以删除* 1.0。

PS: the result of round(avg(column * 1.0), 0) still is a decimal, you can explicit convert it using convert(int, round(avg(column * 1.0), 0), 0) or just let whatever language you are using do the job (it's a implicit conversion)

PS: round的结果(avg(列* 1.0)，0)仍然是小数，您可以使用convert(int, round(avg(列* 1.0)，0)或让您使用的任何语言来做这个工作(它是隐式转换)

#3

Select cast(AVG(columnname) as integer)

#4

this did it:

这是:

CONVERT(int,ROUND(AVG(CAST(COLUMN-NAME AS DECIMAL)) ,0))

转换(int,圆(AVG(CAST(列名为十进制)),0))

isn't there a shorter way of doing it though?

难道没有更短的方法吗?

#5

select cast(avg(a+.5) as int) from 
    (select 1 a union all select 2) b

If you don't like shortcuts, you could use the long way:

如果你不喜欢捷径，你可以走很长的路:

select round(avg(cast(a as real)), 0) 
    from (select 1 a union all select 2) b

#6

The following statements are equivalent:

下列陈述是等价的:

-- the original code
CONVERT(int, ROUND(AVG(CAST(mycolumn AS DECIMAL)) ,0))

-- using '1e0 * column' implicitly converts mycolumn value to float
CONVERT(int, ROUND(AVG(1e0 * mycolumn) ,0))

-- the conversion to INT already rounds the value
CONVERT(INT, AVG(1e0 * mycolumn))

#7

-2

On SQL 2014,

在SQL 2014,

select round(94,-1)
select round(95,-1)

#1