Updated: 6 August 2010
Use AVEDEV to calculate the average of the absolute deviations of data points from their mean. The equation for average deviation is
Syntax
SELECT [wctStatistics].[wct].[AVEDEV] (
<@Known_x_TableName, nvarchar(4000),>
,<@ColumnName, nvarchar(4000),>
,<@GroupedColumnName, nvarchar(4000),>
,<@GroupedColumnValue, sql_variant,>)
Arguments
@Known_x_TableName
the name, as text, of the table or view that contains the known x-values to be used in the AVEDEV calculation.
@ColumnName
the name, as text, of the column in the table or view specified by @Known_x_TableName that contains the known x values to be used in the AVEDEV calculation.
@GroupedColumnName
the name, as text, of the column in the table or view specified by @Known_x_TableName which will be used for grouping the results.
@GroupedColumnValue
the column value to do the grouping on.
Return Types
float
Remarks
• No GROUP BY is required for this function even though it produces aggregated results
• For more complex T-SQL statements, consider using AVDEV_q
Examples
Refer to the table definition and data found here.
To determine the average deviation for all students in all subjects:
SELECT wct.AVEDEV('#s1','grade','',NULL)
This produces the following result
----------------------
3.55294214876033
(1 row(s) affected)
To calculate the average deviation for each subject
SELECT distinct s.SUBJECT, wct.AVEDEV('#s1','grade','subject',s.subject)
from #s1 s
This produces the following result
SUBJECT
-------------------------------------------------- ----------------------
Foreign Language 4.04181818181818
History 2.215
Literature 2.215
Math 3.25818181818182
Science 2.73727272727273
(5 row(s) affected)
To calculate the average deviation for each student
SELECT distinct s.STUDENT, wct.AVEDEV('#s1','grade','STUDENT',s.STUDENT)
from #s1 s
This produces the following result
STUDENT
-------------------------------------------------- ----------------------
Student 01 0.520800000000003
Student 02 1.1032
Student 03 1.34
Student 04 1.5128
Student 05 1.6472
Student 06 1.7656
Student 07 1.8744
Student 08 1.9752
Student 09 2.0672
Student 10 2.1576
Student 11 2.248
Student 12 2.336
Student 13 2.4232
Student 14 2.5128
Student 15 2.604
Student 16 2.7016
Student 17 2.79919999999999
Student 18 2.9128
Student 19 3.0344
Student 20 3.1808
Student 21 3.3696
Student 22 3.6528
(22 row(s) affected)