SQL Server AVG为每一行返回0

Question

我试图在Server上进行一个有点复杂的查询。必须从select中进行选择，以便按别名分组。问题是和计数返回正确的值，但是平均值总是零。如果我把它计算为和(quer.score)/count(quer.score)，它也会返回所有的零。

我做错了什么？

提前感谢！

SELECT quer.month, quer.item_type, SUM(quer.score) AS sum_values,COUNT(quer.score) as count_values, avg(quer.score) as final_value FROM (
SELECT
CASE
WHEN MONTH(inte.date) = 1 THEN 'January'
WHEN MONTH(inte.date) = 2 THEN 'February'
WHEN MONTH(inte.date) = 3 THEN 'March'
WHEN MONTH(inte.date) = 4 THEN 'April'
WHEN MONTH(inte.date) = 5 THEN 'May'
WHEN MONTH(inte.date) = 6 THEN 'June'
WHEN MONTH(inte.date) = 7 THEN 'July'
WHEN MONTH(inte.date) = 8 THEN 'Agosto'
WHEN MONTH(inte.date) = 9 THEN 'September'
WHEN MONTH(inte.date) = 10 THEN 'October'
WHEN MONTH(inte.date) = 11 THEN 'November'
WHEN MONTH(inte.date) = 12 THEN 'December'
END AS month,

CASE
    WHEN  inte.item_id in (SELECT distinct item_id from Items where item_type = 'electronic') THEN 'electronic'
    ELSE 'not electronic'
END AS item_type,

CASE WHEN scores.score <7 THEN -1
WHEN scores.score >8 THEN 1
ELSE 0
END AS score
FROM internal_items inte inner join scores_data scores on inte.item_id = scores.item_id
WHERE inte.internal_type = 'dba'
) quer

GROUP BY quer.month, quer.item_type

结果：

month     item_type           sum  count  avg
January   electronic           0     3     0
January   not electronic      -2     7     0
February  electronic          -4     6     0
February  not electronic      -6     8     0
March     electronic          -4     5     0
March     not electronic      -3     6     0

Answer 1

您应该使用小数或浮点数，而不是整数作为数据类型，例如使用。

CASE WHEN scores.score <7 THEN -1.0
WHEN scores.score >8 THEN 1.0
ELSE 0.0
END AS score

(请注意添加的.0 )。

Server使用相同的数据类型作为聚合结果的基列，因此，当输入值为ints时，平均值将被计算为int。