0

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
EzLo
  • 13,780
  • 10
  • 33
  • 38
aggicd
  • 727
  • 6
  • 28

1 Answers1

1

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
Cato
  • 3,652
  • 9
  • 12
  • well at least I get a value :) but how I can validate it? – aggicd Jul 06 '18 at 11:26
  • 1
    @aggicd pick 3 ownerIDs which you know their counts beforehand and filter them with a `WHERE`. Then see if the result is reasonable or not. – EzLo Jul 06 '18 at 11:34
  • @EzLo (OwnerIDS, Number of items): (101,14),(104,5),(105,2),(106,1),(231,1) and the result I get is 5.50454357780915 – aggicd Jul 06 '18 at 11:39
  • Well I used this : https://www.calculator.net/standard-deviation-calculator.html?numberinputs=14%2C5%2C2%2C1%2C1&x=38&y=34 and it is correct! thank you! – aggicd Jul 06 '18 at 11:41