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 Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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

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







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems