CODEWITHSUNDEEP
sqlsql-serversql-server-2012standard-deviationstdev
aggicd
aggicd

Reputation: 737

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

Upvotes: 0

Views: 124

Answers (1)

Cato
Cato

Reputation: 3701

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

Upvotes: 1

Related Questions