title: Standard deviation within group in SQL or SSRS (alternatively - calculating Sigma or Cpk)
body: <p>I have a serious problem with calculating <em>Standard Deviation within subgroup</em> in SSRS/SQl (whichever is more convenient). The real reason behind this is I need to calculate Sigma value (according to Six Sigma principles) or alternatively Cpk value (process capability), but my efforts stop at the standard deviation.</p>
  
  <p>I'm not strong with statistics but it seems that <em>StDev</em> and <em>StDevP</em> functions in SSRS (and SQL) are not calculated 'within subgroup', and I cannot find a function that could do that. Below is an excel screen shot showing the calculation steps which I would like to achieve in SQL/SSRS
  Edit: Cannot post images yet, please find below</p>
  
  <p><a href="https://i.stack.imgur.com/Sjiva.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Sjiva.png" alt="enter image description here"></a></p>
  
  <p>The tricky part is calculating the sum of absolute differences between each pair of values. The order in which the values are given is significant. I forgot to mention that number 27 in denominator of Rbar (Row 7) is the sample size minus 1 (27). Below is also a table in SQL: </p>
  
  <pre><code>DECLARE @Measurements TABLE(Val FLOAT)
  INSERT INTO @Measurements (Val)(
      SELECT 485
      UNION ALL SELECT 490.6
      UNION ALL SELECT 490.6
      UNION ALL SELECT 485
      UNION ALL SELECT 485
      UNION ALL SELECT 489
      UNION ALL SELECT 485
      UNION ALL SELECT 477
      UNION ALL SELECT 477
      UNION ALL SELECT 484.6
      UNION ALL SELECT 484.6
      UNION ALL SELECT 484.6
      UNION ALL SELECT 484.6
      UNION ALL SELECT 485
      UNION ALL SELECT 484.6
      UNION ALL SELECT 484.6
      UNION ALL SELECT 485
      UNION ALL SELECT 485
      UNION ALL SELECT 477
      UNION ALL SELECT 484.6
      UNION ALL SELECT 484.6
      UNION ALL SELECT 484.6
      UNION ALL SELECT 484.6
      UNION ALL SELECT 484.6
      UNION ALL SELECT 484.6
      UNION ALL SELECT 482
      UNION ALL SELECT 482
      UNION ALL SELECT 482
  )
  SELECT STDEV(Val) FROM @Measurements 
  </code></pre>
  
  <p>I'm guessing that what I need could be achieved by using either RollingValue or some stored procedure in SSRS. Of course if there is a smarter way to calculate Sigma or Cpk I'll be very happy to hear about it.</p>
  
  <p>I hope the question is well formulated, if not please comment. This is very important for me so I will be grateful for any assistance :) Thanks!</p>