﻿ Calculate Median Value of a Numeric List in SQL Server SQL Server and T-SQL Development Tutorials 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 Median Value of a Numeric List in SQL Server

SQL programmers can calculate median value of a numeric array in SQL Server by using Row_Number(), Count() and other SQL Server aggregate functions. Transact-SQL developers or database administrators can use median value calculation method on any numeric table column in a SQL Server database as well as the can calculate the median value of a given list as an input parameter to the SQL median function.

Let's create a database table to store numerical values like an array of numeric values. This value table column can be used for statistical values entry and storage by SQL developers.

Now, let SQL developers or database administrators to generate sample data and insert into our SQL database table for the calculation of median value of numbers column. I used SQL RAND() function to generate random numbers between 0 and 1. By multiplying the random value with 100, I have random values between 0 and 99 for sample test data.

Here is the source codes of the SQL script which can be used to calculate the median value of a numeric value list given on SQL Server.

SQL Server CTE expression counts the numeric values in the sample array and sorts the numeric values by using SQL Row_Number() function in an ascending way.

Statistical median value calculation is done by first marking the values at the middle of the array when the array items are sorted ascending. If the count of the numerical list items is an "odd" number then the value is easily read from the list. On the other hand, if the number or numeric values in the sample array is "even", then the two values that are at the middle are summed and then divided by two.

Here is a screenshot of the above median calculation in SQL Server. Calculate median 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 median value as follows.

Please note that a second SQL function is used to split concatenated numeric values using SQL named dbo.split(). SQL Server developers can find the source codes of the SQL Split function in the reference SQL tutorial.

And this sample SQL function can be used to calculate median value of a numeric list as given in the following example on SQL Server Median value calculation function for SQL Server developers

SQL Server