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

I have a query

我有一个查询

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

Where I'm getting the average of rating 1-5 but I'm also counting the ratings where equal '0'.

我得到1-5的平均值,但我也计算等于'0'的等级。

How can I get it to only count a rating where rating[x] does not equal '0' ? It would still count but except of dividing it by 5 it will divide it by how many columns are not '0'.

我怎样才能只计算评级[x]不等于'0'的评级?它仍然会计数,但除了将其除以5之外,它将除以多少列不为“0”。

I created a < http://sqlfiddle.com/#!3/570fa/7 >

我创建了一个

2 个解决方案

#1

Try using VALUES to get the average of non-0 values for each row.

尝试使用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

I removed the following condition from your WHERE

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

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

Because if you leave it in, you're only calculating averages for rows that have 5 non-0 ratings, which I'm guessing wasn't your intention.

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

To get the average of non-0 ratings across all rows divide the total sum of all non-0 ratings by the total count of non-0 ratings.

要获得所有行的非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

As others have mentioned, you should consider normalizing your table if possible.

正如其他人所提到的,如果可能的话,你应该考虑规范你的表格。

#2

This is not pretty but should work:

这不漂亮,但应该工作:

  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 ...

After doing something like this one should feel appreciation for data normalization.

在做了这样的事情后,我们应该感谢数据规范化。

#1