Logo

SQL Server AVERAGE_q Function

Updated 2023-10-31 15:06:21.847000

Description

Use the scalar function AVERAGE_q to return the average (arithmetic mean) for a dataset using dynamic SQL. The equation for AVERAGE_q is:

\frac{1}{N}\sum_{j=1}^{N}x_j

Syntax

SELECT [westclintech].[wct].[AVERAGE_q] (
   <@Values_RangeQuery, nvarchar(4000),>)

Arguments

@Values_RangeQuery

the select statement, as text, used to determine the values to be used in the AVERAGE_q calculation.

Return Type

float

Remarks

AVERAGE_q is the arithmetic mean and is calculated by adding a group of numbers and then dividing by the count of those numbers. For purposes of this calculation, NULL values are not included.

No GROUP BY is required for this function even though it produces aggregated results.

AVERAGE_q may be used on normalized and de-normalized table, but for simpler queries, consider using AVERAGE.

Examples

Using the normalized table #s1 , calculate the average student grade for the Math.

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 round(wct.AVERAGE_q('Select Grade from #s1 where subject = ' + char(39) + 
          'Math' + Char(39)), 3);

This produces the following result.

column 1
87.822

Using the normalized table #s1 , calculate the average grade for each subject,

SELECT distinct
       s2.subject,
       Round(wct.AVERAGE_q('Select s1.Grade from #s1 s1 where s1.subject = ' + 
                 Char(39) + s2.subject + Char(39)), 3)
from #s1 s2;

This produces the following result

subject
Foreign Language84.897
History91.72
Literature91.72
Math87.822
Science89.77

Using the normalized table #s1 , calculate the average grade for each student.

SELECT distinct
       s2.student,
       Round(wct.AVERAGE_q('Select s1.Grade from #s1 s1 where s1.student = ' + 
                 Char(39) + s2.student + Char(39)), 3)
from #s1 s2;

This produces the following result.

student(No Column Name)
Student 0197.506
Student 0294.734
Student 0393.59
Student 0492.776
Student 0592.114
Student 0691.542
Student 0791.028
Student 0890.554
Student 0990.104
Student 1089.672
Student 1189.25
Student 1288.83
Student 1388.414
Student 1487.986
Student 1587.55
Student 1687.092
Student 1786.604
Student 1886.076
Student 1985.478
Student 2084.776
Student 2183.882
Student 2282.526