SQL Server AVG每一行返回0

Question

我想在SQL Server上做一个有点复杂的查询。为了按别名分组，必须从一个选择中执行一个选择。问题是总和和计数返回正确的值，但平均值总是返回零。如果我计算它为sum(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)。

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