DEVSQ_q
Updated 2024-02-09 21:16:34.817000
Syntax
SELECT [westclintech].[wct].[DEVSQ_q] (
<@Known_x_RangeQuery, nvarchar(4000),>)
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
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.
{"columns":[{"field":"column 1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"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.
{"columns":[{"field":"subject"},{"field":" ","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"subject":"Foreign Language"," ":"553.967036363636"},{"subject":"History"," ":"166.494695454545"},{"subject":"Literature"," ":"166.494695454545"},{"subject":"Math"," ":"360.071127272727"},{"subject":"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
{
"columns": [
{
"field": "student"
},
{
"field": "(No Column Name)",
"headerClass": "ag-right-aligned-header",
"cellClass": "ag-right-aligned-cell"
}
],
"rows": [
{
"student": "Student 01",
"(No Column Name)": "1.77212000000002"
},
{
"student": "Student 02",
"(No Column Name)": "7.96292"
},
{
"student": "Student 03",
"(No Column Name)": "11.7302"
},
{
"student": "Student 04",
"(No Column Name)": "14.94752"
},
{
"student": "Student 05",
"(No Column Name)": "17.74432"
},
{
"student": "Student 06",
"(No Column Name)": "20.39848"
},
{
"student": "Student 07",
"(No Column Name)": "23.00088"
},
{
"student": "Student 08",
"(No Column Name)": "25.52052"
},
{
"student": "Student 09",
"(No Column Name)": "27.96032"
},
{
"student": "Student 10",
"(No Column Name)": "30.44448"
},
{
"student": "Student 11",
"(No Column Name)": "33.0344"
},
{
"student": "Student 12",
"(No Column Name)": "35.7018"
},
{
"student": "Student 13",
"(No Column Name)": "38.39332"
},
{
"student": "Student 14",
"(No Column Name)": "41.27792"
},
{
"student": "Student 15",
"(No Column Name)": "44.3702"
},
{
"student": "Student 16",
"(No Column Name)": "47.7230800000001"
},
{
"student": "Student 17",
"(No Column Name)": "51.2705199999999"
},
{
"student": "Student 18",
"(No Column Name)": "55.4961200000001"
},
{
"student": "Student 19",
"(No Column Name)": "60.2548799999999"
},
{
"student": "Student 20",
"(No Column Name)": "66.1875200000001"
},
{
"student": "Student 21",
"(No Column Name)": "74.28668"
},
{
"student": "Student 22",
"(No Column Name)": "87.2487200000002"
}
]
}