SQL Server DEVSQ_q Function
Updated 2024-02-09 21:16:34.817000
Description
Use the scalar function DEVSQ _q to calculate the sum of the squares of deviations of data points from their sample mean. The equation for deviation squared is
{DEVSQ}=\sum(x-\bar{x})^2
Syntax
SELECT [westclintech].[wct].[DEVSQ_q] (
<@Known_x_RangeQuery, nvarchar(4000),>)
Arguments
@Known_x_RangeQuery
the select statement, as text, used to determine the known x-values to be used in the DEVSQ_q calculation.
Return Type
float
Remarks
No GROUP BY is required for this function even though it produces aggregate results.
Examples
To determine the deviation squared for all students in all subjects:
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);
SELECT wct.DEVSQ_q('Select grade from #s1');
This produces the following result.
| column 1 |
|---|
| 2236.75490545455 |
To calculate the deviation squared for each subject.
SELECT Distinct
s.subject,
wct.DEVSQ_q('Select grade from #s1 where #s1.subject = ' + char(39) +
s.subject + char(39))
from #s1 s;
This produces the following result.
| subject | |
|---|---|
| Foreign Language | 553.967036363636 |
| History | 166.494695454545 |
| Literature | 166.494695454545 |
| Math | 360.071127272727 |
| Science | 254.2028 |
To calculate the deviation squared for each student.
SELECT distinct s.student
,wct.DEVSQ_q('Select grade from #s1 where student = ' + char(39) + s.student + char(39))
from #s1 s;
This produces the following result
| student | (No Column Name) |
|---|---|
| Student 01 | 1.77212000000002 |
| Student 02 | 7.96292 |
| Student 03 | 11.7302 |
| Student 04 | 14.94752 |
| Student 05 | 17.74432 |
| Student 06 | 20.39848 |
| Student 07 | 23.00088 |
| Student 08 | 25.52052 |
| Student 09 | 27.96032 |
| Student 10 | 30.44448 |
| Student 11 | 33.0344 |
| Student 12 | 35.7018 |
| Student 13 | 38.39332 |
| Student 14 | 41.27792 |
| Student 15 | 44.3702 |
| Student 16 | 47.7230800000001 |
| Student 17 | 51.2705199999999 |
| Student 18 | 55.4961200000001 |
| Student 19 | 60.2548799999999 |
| Student 20 | 66.1875200000001 |
| Student 21 | 74.28668 |
| Student 22 | 87.2487200000002 |