Solved

# STDEVP in SQL

Posted on 2016-10-24
110 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
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 28

Expert Comment

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

LVL 28

Accepted Solution

Pawan Kumar earned 500 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.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

T-sql Date Format 9 67
SQL parsing XML works but want to do it another way 4 48
Checking for column changes SQL 2014 4 42
MS Access Duplicate Data Assistance 9 32
Iteration: Iteration is repetition of a process. A student who goes to school repeats the process of going to school everyday until graduation. We go to grocery store at least once or twice a month to buy products. We repeat this process every mont…
When there is a disconnect between the intentions of their creator and the recipient, when algorithms go awry, they can have disastrous consequences.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Why Skyport?
###### Suggested Courses
Course of the Month2 days, 18 hours left to enroll