sqlsql-serversql-server-2012standard-deviationstdev

SQL SERVER 2012: Standard Deviation of rows per person


I have table ITEMS:

Itemid, 
ItemName, 
OwnerID

Each row represents an item. An Owner can have multiple items. I want to calculate the standard deviation of items/rows per owner. Is it possible to calculate that using the STDEV built in function?

I tried that but it doesn't seem ok:

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

Solution

  • I think

        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
    

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

    if you ran this to see some data

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

    then you could do

    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