SQL Server AVERAGE Function

Updated 2023-10-31 15:04:21.310000

Description

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

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

Syntax

SELECT [westclintech].[wct].[AVERAGE] (
  <@Values_TableName, nvarchar(4000),>
 ,<@Values_ColumnName, nvarchar(4000),>
 ,<@Values_GroupedColumnName, nvarchar(4000),>
 ,<@Values_GroupedColumnValue, sql_variant,>)

Arguments

@Values_TableName

the name, as text, of the table or view that contains the values to be used in the AVERAGE calculation.

@Values_ColumnName

the name, as text, of the column in the table or view specified by @Values_TableName that contains the known x values to be used in the AVERAGE calculation.

@Values_GroupedColumnName

the name, as text, of the column in the table or view specified by @Values_TableName which will be used for grouping the results.

@Values_GroupedColumnValue

the column value to do the grouping on.

Return Type

float

Remarks

AVERAGE 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 with this function even though it produces aggregated results.

AVERAGE may be used on normalized and de-normalized table, but for more complex queries, consider user AVERAGE_q

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('#s1', 'Grade', 'subject', 'Math'), 3);

This produces the following result

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

SELECT distinct
       s.subject,
       round(wct.AVERAGE('#s1', 'Grade', 'subject', s.subject), 3)
from #s1 s;

This produces the following result

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

SELECT distinct
       s.student,
       round(wct.AVERAGE('#s1', 'Grade', 'student', s.student), 3)
from #s1 s;

This produces the following result