如何得到列值不等于零的平均值

Question

我有一个查询

Select AVG(((cast(c.rating1 as Float)+ cast(c.rating2 as Float)+cast (c.rating3 as Float)+cast(c.rating4 as Float)+cast(c.rating5 as Float))/5))
From CSEReduxResponses c
Where 
     c.execoffice_date BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, @today)-1, 0)
AND DATEADD(MONTH, DATEDIFF(MONTH, -1, @today)-1, -1)
AND c.execoffice_status =1
AND C.rating1 <> 0 AND C.rating2 <> 0 AND C.rating3 <> 0 AND C.rating4 <> 0 AND 
C.rating5 <> 0

我得到了1-5的平均评分，但我也计算了等于'0'的评分。

我怎样才能让它只计算一个评级，其中评级[x]不等于'0' ?它仍然会计数，但除了除以5之外，它将除以非'0'的列数。

我创建了

Answer 1

这是不漂亮的但应该工作:

  SELECT AVG((... SUM of 5 ratings ...) /
           (CASE WHEN rating1 > 0 THEN 1 ELSE 0 END + 
            CASE WHEN rating2 > 0 THEN 1 ELSE 0 END +
            CASE WHEN rating3 > 0 THEN 1 ELSE 0 END +
            CASE WHEN rating4 > 0 THEN 1 ELSE 0 END +
            CASE WHEN rating5 > 0 THEN 1 ELSE 0 END))
  FROM ...
  WHERE ...

在做了这样的事情之后，应该会对数据规范化感到感激。

Answer 2

尝试使用VALUES获取每行非0值的平均值。

SELECT
(
    SELECT AVG(rating)
    FROM (VALUES 
        (cast(c.rating1 as Float)), 
        (cast(c.rating2 as Float)), 
        (cast(c.rating3 as Float)), 
        (cast(c.rating4 as Float)), 
        (cast(c.rating5 as Float))
    ) AS v(rating)
    WHERE v.rating <> 0
) avg_rating
From CSEReduxResponses c
Where 
     c.execoffice_date BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, @today)-1, 0)
AND DATEADD(MONTH, DATEDIFF(MONTH, -1, @today)-1, -1)
AND c.execoffice_status = 1

http://sqlfiddle.com/!3/570fa/26

我从你的WHERE中删除了以下条件

AND C.rating1 <> 0 AND C.rating2 <> 0 AND C.rating3 <> 0 AND C.rating4 <> 0 AND 
C.rating5 <> 0

因为如果您留下它，您只计算具有5个非0评级的行的平均值，我猜这不是您的意图。

要得到所有行的非0评分的平均值，将所有非0评分的总和除以非0评分的总数。

SELECT
    CAST(SUM(sum_rating) as float)/SUM(count_rating)
From CSEReduxResponses c
CROSS APPLY (
    SELECT SUM(rating), COUNT(rating)
    FROM (VALUES 
        (c.rating1), 
        (c.rating2), 
        (c.rating3), 
        (c.rating4), 
        (c.rating5)
    ) AS v(rating)
    WHERE v.rating <> 0
) AS x (sum_rating, count_rating)
Where 
    c.execoffice_date BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)
AND DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)
AND c.execoffice_status = 1

http://sqlfiddle.com/!3/570fa/35

正如其他人提到的，如果可能的话，您应该考虑规范化您的表。