x
Solved

# STDEVP in SQL

Posted on 2016-10-24
Medium Priority
147 Views
I have data that is arranged this way (see below) and need to calculate the Dispersion = Standard Deviation/Average of Total_NR rolled up by the MGR_NAME. How can I achieve this in T-SQL? Thank you.

The way it's supposed to work is: Sum (Total_NR) divided by the average of the sum of NR which we should then so a STDEVP on that average.

MGR_NAME      EMPL      HC      Total_NR           Avrg             Dispersion?
JOSE PH        1027         1           \$3,974.00          0.000107
JOSE PH      1233      1           \$3,324.00          0.000074
JOSE PH      1354      1         \$51.87                  0.000012
JOSE PH      1375      1      \$2,302.67          0.000049
JOSE PH      1486      1      \$1,230.68          0.000045
0
Question by:saved4use
• 2

LVL 38

Expert Comment

ID: 41857935
can you pls post the expected output for Dispersion column?
0

LVL 38

Accepted Solution

Pawan Kumar earned 2000 total points
ID: 41857941
Try..

``````SELECT stdddv.*,AVG(Avrg) OVER (PARTITION BY MGR_NAME) AvgByManagerName , ny StandardDeviation, ny1  StandardDeviation2 ,
ny/AVG(Avrg) OVER (PARTITION BY MGR_NAME) Dispersion1, ny1/AVG(Avrg) OVER (PARTITION BY MGR_NAME) Dispersion2 FROM stdddv
CROSS APPLY
(
SELECT STDEV(Total_NR) ny , STDEVP(Total_NR) ny1
FROM stdddv
)b
``````

OR....

``````SELECT stdddv.*,AVG(Avrg) OVER (PARTITION BY MGR_NAME) AvgByManagerName , STDEVP(Total_NR) OVER(PARTITION BY MGR_NAME) StandardDeviation, STDEV(Total_NR) OVER(PARTITION BY MGR_NAME)  StandardDeviation2 ,
STDEVP(Total_NR) OVER(PARTITION BY MGR_NAME)/AVG(Avrg) OVER (PARTITION BY MGR_NAME) Dispersion1, STDEV(Total_NR) OVER(PARTITION BY MGR_NAME) /AVG(Avrg) OVER (PARTITION BY MGR_NAME) Dispersion2
FROM stdddv
``````
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.