AVEDEV_q
Updated 2023-10-20 21:26:51.627000
Syntax
SELECT [westclintech].[wct].[AVEDEV_q] (
<@Known_x_RangeQuery, nvarchar(4000),> )
Description
Use AVEDEV_q to calculate the average of the absolute deviations of data points from their mean. The equation for average deviation is
\frac{1}{N}\sum_{j=0}^{N-1}|x_j-\bar{x}|
Arguments
@Known_x_RangeQuery
the select statement, as text, used to determine the known x-values to be used in the AVEDEV_q calculation.
Return Type
float
Remarks
No GROUP BY is required for this function even though it produces aggregated results.
Examples
CREATE TABLE #s1
(
[Student] [nvarchar](50) NOT NULL,
[subject] [nvarchar](50) NOT NULL,
[grade] [float] NULL
);
INSERT INTO #S1
VALUES
('Student 01', 'Math', 97.19);
INSERT INTO #S1
VALUES
('Student 02', 'Math', 94.07);
INSERT INTO #S1
VALUES
('Student 03', 'Math', 92.78);
INSERT INTO #S1
VALUES
('Student 04', 'Math', 91.86);
INSERT INTO #S1
VALUES
('Student 05', 'Math', 91.12);
INSERT INTO #S1
VALUES
('Student 06', 'Math', 90.48);
INSERT INTO #S1
VALUES
('Student 07', 'Math', 89.9);
INSERT INTO #S1
VALUES
('Student 08', 'Math', 89.36);
INSERT INTO #S1
VALUES
('Student 09', 'Math', 88.86);
INSERT INTO #S1
VALUES
('Student 10', 'Math', 88.37);
INSERT INTO #S1
VALUES
('Student 11', 'Math', 87.89);
INSERT INTO #S1
VALUES
('Student 12', 'Math', 87.42);
INSERT INTO #S1
VALUES
('Student 13', 'Math', 86.95);
INSERT INTO #S1
VALUES
('Student 14', 'Math', 86.47);
INSERT INTO #S1
VALUES
('Student 15', 'Math', 85.98);
INSERT INTO #S1
VALUES
('Student 16', 'Math', 85.46);
INSERT INTO #S1
VALUES
('Student 17', 'Math', 84.92);
INSERT INTO #S1
VALUES
('Student 18', 'Math', 84.32);
INSERT INTO #S1
VALUES
('Student 19', 'Math', 83.65);
INSERT INTO #S1
VALUES
('Student 20', 'Math', 82.86);
INSERT INTO #S1
VALUES
('Student 21', 'Math', 81.85);
INSERT INTO #S1
VALUES
('Student 22', 'Math', 80.32);
INSERT INTO #S1
VALUES
('Student 01', 'Science', 97.64);
INSERT INTO #S1
VALUES
('Student 02', 'Science', 95.02);
INSERT INTO #S1
VALUES
('Student 03', 'Science', 93.94);
INSERT INTO #S1
VALUES
('Student 04', 'Science', 93.17);
INSERT INTO #S1
VALUES
('Student 05', 'Science', 92.54);
INSERT INTO #S1
VALUES
('Student 06', 'Science', 92);
INSERT INTO #S1
VALUES
('Student 07', 'Science', 91.51);
INSERT INTO #S1
VALUES
('Student 08', 'Science', 91.06);
INSERT INTO #S1
VALUES
('Student 09', 'Science', 90.64);
INSERT INTO #S1
VALUES
('Student 10', 'Science', 90.23);
INSERT INTO #S1
VALUES
('Student 11', 'Science', 89.83);
INSERT INTO #S1
VALUES
('Student 12', 'Science', 89.43);
INSERT INTO #S1
VALUES
('Student 13', 'Science', 89.04);
INSERT INTO #S1
VALUES
('Student 14', 'Science', 88.64);
INSERT INTO #S1
VALUES
('Student 15', 'Science', 88.22);
INSERT INTO #S1
VALUES
('Student 16', 'Science', 87.79);
INSERT INTO #S1
VALUES
('Student 17', 'Science', 87.33);
INSERT INTO #S1
VALUES
('Student 18', 'Science', 86.83);
INSERT INTO #S1
VALUES
('Student 19', 'Science', 86.26);
INSERT INTO #S1
VALUES
('Student 20', 'Science', 85.6);
INSERT INTO #S1
VALUES
('Student 21', 'Science', 84.75);
INSERT INTO #S1
VALUES
('Student 22', 'Science', 83.47);
INSERT INTO #S1
VALUES
('Student 01', 'Foreign Language', 96.52);
INSERT INTO #S1
VALUES
('Student 02', 'Foreign Language', 92.64);
INSERT INTO #S1
VALUES
('Student 03', 'Foreign Language', 91.05);
INSERT INTO #S1
VALUES
('Student 04', 'Foreign Language', 89.91);
INSERT INTO #S1
VALUES
('Student 05', 'Foreign Language', 88.99);
INSERT INTO #S1
VALUES
('Student 06', 'Foreign Language', 88.19);
INSERT INTO #S1
VALUES
('Student 07', 'Foreign Language', 87.47);
INSERT INTO #S1
VALUES
('Student 08', 'Foreign Language', 86.81);
INSERT INTO #S1
VALUES
('Student 09', 'Foreign Language', 86.18);
INSERT INTO #S1
VALUES
('Student 10', 'Foreign Language', 85.58);
INSERT INTO #S1
VALUES
('Student 11', 'Foreign Language', 84.99);
INSERT INTO #S1
VALUES
('Student 12', 'Foreign Language', 84.4);
INSERT INTO #S1
VALUES
('Student 13', 'Foreign Language', 83.82);
INSERT INTO #S1
VALUES
('Student 14', 'Foreign Language', 83.22);
INSERT INTO #S1
VALUES
('Student 15', 'Foreign Language', 82.61);
INSERT INTO #S1
VALUES
('Student 16', 'Foreign Language', 81.97);
INSERT INTO #S1
VALUES
('Student 17', 'Foreign Language', 81.29);
INSERT INTO #S1
VALUES
('Student 18', 'Foreign Language', 80.55);
INSERT INTO #S1
VALUES
('Student 19', 'Foreign Language', 79.72);
INSERT INTO #S1
VALUES
('Student 20', 'Foreign Language', 78.74);
INSERT INTO #S1
VALUES
('Student 21', 'Foreign Language', 77.49);
INSERT INTO #S1
VALUES
('Student 22', 'Foreign Language', 75.6);
INSERT INTO #S1
VALUES
('Student 01', 'History', 98.09);
INSERT INTO #S1
VALUES
('Student 02', 'History', 95.97);
INSERT INTO #S1
VALUES
('Student 03', 'History', 95.09);
INSERT INTO #S1
VALUES
('Student 04', 'History', 94.47);
INSERT INTO #S1
VALUES
('Student 05', 'History', 93.96);
INSERT INTO #S1
VALUES
('Student 06', 'History', 93.52);
INSERT INTO #S1
VALUES
('Student 07', 'History', 93.13);
INSERT INTO #S1
VALUES
('Student 08', 'History', 92.77);
INSERT INTO #S1
VALUES
('Student 09', 'History', 92.42);
INSERT INTO #S1
VALUES
('Student 10', 'History', 92.09);
INSERT INTO #S1
VALUES
('Student 11', 'History', 91.77);
INSERT INTO #S1
VALUES
('Student 12', 'History', 91.45);
INSERT INTO #S1
VALUES
('Student 13', 'History', 91.13);
INSERT INTO #S1
VALUES
('Student 14', 'History', 90.8);
INSERT INTO #S1
VALUES
('Student 15', 'History', 90.47);
INSERT INTO #S1
VALUES
('Student 16', 'History', 90.12);
INSERT INTO #S1
VALUES
('Student 17', 'History', 89.74);
INSERT INTO #S1
VALUES
('Student 18', 'History', 89.34);
INSERT INTO #S1
VALUES
('Student 19', 'History', 88.88);
INSERT INTO #S1
VALUES
('Student 20', 'History', 88.34);
INSERT INTO #S1
VALUES
('Student 21', 'History', 87.66);
INSERT INTO #S1
VALUES
('Student 22', 'History', 86.62);
INSERT INTO #S1
VALUES
('Student 01', 'Literature', 98.09);
INSERT INTO #S1
VALUES
('Student 02', 'Literature', 95.97);
INSERT INTO #S1
VALUES
('Student 03', 'Literature', 95.09);
INSERT INTO #S1
VALUES
('Student 04', 'Literature', 94.47);
INSERT INTO #S1
VALUES
('Student 05', 'Literature', 93.96);
INSERT INTO #S1
VALUES
('Student 06', 'Literature', 93.52);
INSERT INTO #S1
VALUES
('Student 07', 'Literature', 93.13);
INSERT INTO #S1
VALUES
('Student 08', 'Literature', 92.77);
INSERT INTO #S1
VALUES
('Student 09', 'Literature', 92.42);
INSERT INTO #S1
VALUES
('Student 10', 'Literature', 92.09);
INSERT INTO #S1
VALUES
('Student 11', 'Literature', 91.77);
INSERT INTO #S1
VALUES
('Student 12', 'Literature', 91.45);
INSERT INTO #S1
VALUES
('Student 13', 'Literature', 91.13);
INSERT INTO #S1
VALUES
('Student 14', 'Literature', 90.8);
INSERT INTO #S1
VALUES
('Student 15', 'Literature', 90.47);
INSERT INTO #S1
VALUES
('Student 16', 'Literature', 90.12);
INSERT INTO #S1
VALUES
('Student 17', 'Literature', 89.74);
INSERT INTO #S1
VALUES
('Student 18', 'Literature', 89.34);
INSERT INTO #S1
VALUES
('Student 19', 'Literature', 88.88);
INSERT INTO #S1
VALUES
('Student 20', 'Literature', 88.34);
INSERT INTO #S1
VALUES
('Student 21', 'Literature', 87.66);
INSERT INTO #S1
VALUES
('Student 22', 'Literature', 86.62);
To determine the average deviation for all students in all subjects :
SELECT wct.AVEDEV_q('Select grade from #s1');
This produces the following result
{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"column 1":"3.55294214876033"}]}
To calculate the average deviation for each subject
SELECT Distinct
s.subject,
wct.AVEDEV_q('Select grade from #s1 where #s1.subject = ' + char(39) +
s.subject + char(39)) as AVEDEV
from #s1 s;
This produces the following result.
subject AVEDEV
-------------------------------------------------- ----------------------
Foreign Language 4.04181818181818
History 2.215
Literature 2.215
Math 3.25818181818182
Science 2.73727272727273
To calculate the average deviation for each student
SELECT distinct
s.student,
wct.AVEDEV_q('Select grade from #s1 where student = ' + char(39) + s.student
+ char(39)) AS AVEDEV
from #s1 s;
This produces the following result
student AVEDEV
-------------------------------------------------- ----------------------
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