SQL SERVER 2012:每人行的标准偏差

Question

我有表格ITEMS：

Itemid, 
ItemName, 
OwnerID

每行代表一个项目。一个所有者可以有多个项目。我想计算每个所有者的项目/行的标准偏差。是否可以使用内置STDEV函数进行计算？

我试过了，但似乎不行：

select STDEV(COUNT(*)) from dbo.ITEMS 
GROUP BY ITEMS.OwnerID

Answer 1

我认为

SELECT STDEV(IQ.cnt) as SDofCounts from --'from' was missing
(select ITEMS.OwnerID, COUNT(0) as cnt from dbo.ITEMS 
GROUP BY ITEMS.OwnerID
) IQ

++++++++++++++++

如果您运行此命令以查看一些数据

select ITEMS.OwnerID, COUNT(0) as cnt, COUNT(0) * COUNT(0) as EXS   from dbo.ITEMS 
GROUP BY ITEMS.OwnerID

然后你可以做

SELECT STDEV(IQ.cnt) as SDofCounts, 
SUM(EXS) as SigmaEXsquare, 
SUM(1.0 * cnt) / COUNT(0) as MU, 
SUM(EXS) - (SUM(1.0 * cnt) / COUNT(0)) * (SUM(1.0 * cnt) / COUNT(0)) as Variance, 
SQRT(SUM(EXS) - (SUM(1.0 * cnt) / COUNT(0)) * (SUM(1.0 * cnt) / COUNT(0))) AS SDcalc
from
(select ITEMS.OwnerID, COUNT(0) as cnt, COUNT(0) * COUNT(0) as EXS from dbo.ITEMS 
GROUP BY ITEMS.OwnerID
) IQ