SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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
)
Code

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
Code

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
Code

Here is a screenshot of the above mean calculation or average calculation in SQL Server.

calculate mean value 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
Code

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]
Code

SQL mean value calculation function in SQL Server
Mean value (average) calculation function for SQL Server developers



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.