Calculate Mean Value in SQL Server
SQL programmers can calculate mean value of a numeric array in SQL Server by using SQL Server AVG() aggregate function. Transact-SQL developers or database administrators can use mean value calculation method on any numeric table column in a SQL Server database as shown in this SQL tutorial.
First of all let SQL database administrators create database table to store sample numeric values where programmers can calculate the mean value of the numeric values.
create table StatisticalNumbers(
value int
)
Let's now generate sample data and populate sql table.
SQL developers and database administrators can to calculate the mean value of the values stored in this table column.
Here is a SQL script which will create 5 random numbers using SQL Rand() function and insert into database table.
declare @i int = 1
while @i <= 5 -- count of sample numbers
begin
insert into StatisticalNumbers SELECT RAND()*10 -- (10-1) is max number
set @i=@i+1
end
Here is the source codes of the SQL Select script which can be used to calculate the mean value of a numeric value list given on SQL Server.
What is important about the below SQL mean value calculation using AVG() SQL aggregate function is multiplying the values with 1.0 beforehand for decimal value result.
As seen below the following two average calculations return integer values losing the decimal points return false values.
select
avg(value * 1.0) [mean average value],
avg(value) * 1.0, -- false calculation
avg(value) -- false calculation
from StatisticalNumbers
Here is a screenshot of the above mean calculation or average calculation in SQL Server.
Calculate mean value of an array in SQL Server
It is also possible to create a user defined SQL function which takes the numeric values as a string input parameter formed of concatenated numeric values and returns the mean value as follows.
Since SQL aggregate function does not accept multiple values I had to create this SQL mean value calculation function.
In the Mean calculation function, a second user defined SQL function dbo.split() is used to split concatenated numeric values which are passed as input to the SQL mean function. SQL Server developers and administrators can check SQL tutorial SQL Split function for the source codes of the helper function.
create function udf_calculate_mean (
@numericValueList varchar(max)
) returns float
as
begin
declare @mean float
select @mean = avg(cast(val as int) * 1.0 ) from dbo.Split(@numericValueList,',')
return @mean
end
And this sample SQL function can be used to calculate mean value of a numeric list or average value of items as given in the following example on SQL Server
declare @numericValueList varchar(max) = '1,4,0,2'
select dbo.udf_calculate_mean(@numericValueList) [Mean Value]
Mean value (average) calculation function for SQL Server developers