SQL Server AVG和Excel AVERAGE产生不同的结果?
问题描述:
我想在SQL服务器上显示平均值,但是当我在Excel中测试数据时结果不一样,必须有一些明显的我缺少的东西。SQL Server AVG和Excel AVERAGE产生不同的结果?
下面是从SQL Server的代码和结果:
SELECT DISTINCT
d.d_reference + ' - ' + d.d_name AS Faculty,
AVG(sub.GroupSize) AS FacultyAverage
FROM
unitesnapshot.dbo.capd_register r
INNER JOIN unitesnapshot.dbo.capd_studentregister sr ON sr.sr_register = r.r_id
INNER JOIN unitesnapshot.dbo.capd_activity a ON a.a_register = r.r_id
INNER JOIN unitesnapshot.dbo.capd_moduleactivity ma ON ma.ma_activity = a.a_id
INNER JOIN unitesnapshot.dbo.capd_module m ON m.m_id = ma.ma_activitymodule
INNER JOIN unitesnapshot.dbo.capd_department d ON d.d_id = m.m_moduledept
INNER JOIN unitesnapshot.dbo.capd_section sec ON sec.s_id = m.m_modulesection
INNER JOIN (SELECT
r.r_reference,
COUNT(DISTINCT s.s_studentreference) AS GroupSize
FROM
unitesnapshot.dbo.capd_student s
INNER JOIN unitesnapshot.dbo.capd_person p ON p.p_id = s.s_id
INNER JOIN unitesnapshot.dbo.capd_studentregister sr ON sr.sr_student = p.p_id
INNER JOIN unitesnapshot.dbo.capd_register r ON r.r_id = sr.sr_register
GROUP BY
r.r_reference) sub ON sub.r_reference = r.r_reference
WHERE
SUBSTRING(r.r_reference,4,2) = '12' AND
d.d_reference = '730'
GROUP BY
d.d_reference,
d.d_name
这是效果在Excel中:
感谢
答
尝试此好玩:
select avg(a)
from
(values(1),(2),(3),(4)) x(a);
avg(a)
-------
2
AVG()返回与基列相同的数据类型。如果您的列的类型为int,那么结果也会被截断为int。以下返回“正确”的结果。
select avg(cast(a as decimal(10,5)))
from
(values(1),(2),(3),(4)) x(a);
result
--------
2.5
你呈现的差异(24 VS 19.50484)将最有可能涉及与此相伴随的另一个错误。例如,要检查您是否在Excel中总结了与SQL Server中相同的数据,请将此结果转储到Excel中并对其进行总结。如果它与您目前认为的SQL Server数据的Excel 等效的不匹配,请将列对齐并检查它们是否具有相同的行数。然后按值ASCENDING分别对每列进行排序并再次比较。
SELECT d.d_name, sub.GroupSize AS FacultyAverage
FROM unitesnapshot.dbo.capd_register r
INNER JOIN unitesnapshot.dbo.capd_studentregister sr ON sr.sr_register = r.r_id
INNER JOIN unitesnapshot.dbo.capd_activity a ON a.a_register = r.r_id
INNER JOIN unitesnapshot.dbo.capd_moduleactivity ma ON ma.ma_activity = a.a_id
INNER JOIN unitesnapshot.dbo.capd_module m ON m.m_id = ma.ma_activitymodule
INNER JOIN unitesnapshot.dbo.capd_department d ON d.d_id = m.m_moduledept
INNER JOIN unitesnapshot.dbo.capd_section sec ON sec.s_id = m.m_modulesection
INNER JOIN (SELECT r.r_reference,
COUNT(DISTINCT s.s_studentreference) AS GroupSize
FROM unitesnapshot.dbo.capd_student s
INNER JOIN unitesnapshot.dbo.capd_person p ON p.p_id = s.s_id
INNER JOIN unitesnapshot.dbo.capd_studentregister sr ON sr.sr_student = p.p_id
INNER JOIN unitesnapshot.dbo.capd_register r ON r.r_id = sr.sr_register
GROUP BY r.r_reference) sub ON sub.r_reference = r.r_reference
WHERE SUBSTRING(r.r_reference,4,2) = '12' AND d.d_reference = '730'
ORDER BY d.d_name
感谢您的回答理查德,但我只是试过这个,它不工作,它只是产生24.412407的十进制结果?有任何想法吗? – Will 2013-04-24 10:20:45
我强调了Excel的AVERAGE产生一个实数(浮点数)的事实,但SQL Server将遵循基本列的类型。我已经评论说你还有其他问题。在不显示所有数据的情况下,无法检查。有很多方法可以将数据从SQL Server复制到Excel。 – RichardTheKiwi 2013-04-24 10:22:20
谢谢,excel数据是从我原来的问题中查询的一个细微变化产生的,所以数据应该是准确的。 – Will 2013-04-24 10:32:23