# Script to calculate the Median value for SQL Server data

By:   |   Comments (14)   |   Related: > Functions System

##### Problem

The standard SQL language has a number of aggregate functions like: SUM, MIN, MAX, AVG, but a common statistics function that SQL Server does not have is a built-in aggregate function for median. The median is the value that falls in the middle of a sorted resultset with equal parts that are smaller and equal parts that are greater. Since there is no built-in implementation for the median, the following is a simple solution I put together to find the median.

##### Solution

The general solution I'm suggesting here uses a stored procedure I created called dbo.sp_calc_median.  The need I had was to just find the median value for various resultsets, so I put together this stored procedure that could be used for just about anything.  The procedure takes a table name and a column name to calculate the median value for any data type.

The procedure builds an ordered temporary table of the column values and by using a dynamic scrollable cursor, scrolls to the middle of the result set and outputs the result.

Here is the stored procedure:

```create procedure dbo.sp_calc_median
(@tablename varchar(50),
@columnname varchar(50),
@result sql_variant OUTPUT)
as
begin
declare @sqlstmt varchar(200)
declare @midCount int
set nocount on
set @sqlstmt = 'insert #tempmedian select ' + @columnname +
' from ' + @tablename + ' order by 1 asc '
create table #tempmedian (col sql_variant)
exec (@sqlstmt)
declare c_med cursor scroll for select * from #tempmedian
select @midCount = round ( count(*) * 0.5,0 ) from #tempmedian
open c_med
fetch absolute @midCount from c_med into @result
close c_med
deallocate c_med
drop table #tempmedian
end
go```

Here is small sample data set and sample executions, so it is easy to see that this is working.

```--sample table
CREATE TABLE [dbo].[TestTable](
[id] [int] IDENTITY(1,1) NOT NULL,
[testID] [int] NULL,
[testName] [varchar](50) NULL,
[testDate] [date] NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
([id] ASC) )
-- sample data
INSERT INTO TestTable (testID, testName, testDate)
SELECT 1, 'Dave', '2000-11-01' UNION
SELECT 2, 'Mike', '1995-01-11' UNION
SELECT 3, 'Sue' , '1965-07-14' UNION
SELECT 4, 'Jill', '2001-03-07' UNION
SELECT 5, 'Abe' , '2005-09-13'
-- sample run
DECLARE @result sql_variant
EXEC dbo.sp_calc_median 'dbo.TestTable', 'testID', @result OUTPUT
SELECT @result
EXEC dbo.sp_calc_median 'dbo.TestTable', 'testName', @result OUTPUT
SELECT @result
EXEC dbo.sp_calc_median 'dbo.TestTable', 'testDate', @result OUTPUT
SELECT @result
-- output from above run
3
Jill
2000-11-01
```

With this small dataset it is easy to see how these values are determined as shown below where the values are sorted.

• testID - 1, 2, 3, 4, 5
• testName - Abe, Dave, Jill, Mike, Sue
• testDate - 1965-07-14, 1995-01-11, 2000-11-01, 2001-03-07, 2005-09-13

Here is another sample run I did against the Northwind database. This table has more data than the sample above, but works just the same. Also note that I am passing in the database name along with the table, so this stored procedure could be created in one database on your SQL Server instance and used for any database and table.

```DECLARE @res sql_variant -- declaring a sql_variant column
EXEC sp_calc_median 'northwind..products','productname',@res OUTPUT
PRINT convert(nvarchar,@res)
EXEC sp_calc_median 'northwind..products','UnitPrice',@res OUTPUT
PRINT convert(real,@res)
-- output from above run
Maxilaku
19.5
```
##### Next Steps
• Create the sp_calc_median stored procedure and test against your data.
• Look at other ways you could use this in your environment.
• There are several other ways to determine the median value, but this was one simple solution that satisfied my needs.  Hopefully this solution gets the wheels turning.
• Check out these related tips

Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips

 Tuesday, July 30, 2013 - 9:27:01 AM - Jerry Day Back To Top (26059) select avg (vch) from((SELECT MAX(testid) AS vch FROM (SELECT TOP(50) PERCENT testid FROM TestTable ORDER BY testid ASC)  A) UNION ALL(SELECT MIN(testid) as vch FROM (SELECT TOP(50) PERCENT testid FROM TestTable ORDER BY testid DESC) B))  X

 Friday, February 1, 2013 - 8:45:27 PM - Bhavesh Back To Top (21868) Hi Anal,    Can we convert this into a function, do you have any suggestions to add 5 and 95 percentiles.  This is kind of urgent   Thanks, Bhavesh

 Friday, July 13, 2012 - 7:01:10 AM - Anal Patel Back To Top (18493) --** Procedure to Find Median,1st Quartile,3rd Quartile,Minimum and MaximumALTER procedure dbo.sp_calc_median (@tablename varchar(50),  @columnname varchar(50),  @result sql_variant OUTPUT)asBEGIN    declare @sqlstmt varchar(200);  declare @stmt nvarchar(max);  DECLARE @rCount INT;  DECLARE @mPos1 INT;  DECLARE @mPos2 INT;  DECLARE @Q1 INT;  DECLARE @Q2 INT;  DECLARE @Q3 INT;  DECLARE @Q4 INT;  DECLARE @result1 SQL_VARIANT;  DECLARE @result2 sql_variant;  DECLARE @resultQ1 sql_variant;  DECLARE @resultQ2 sql_variant;  DECLARE @resultQ3 sql_variant;  DECLARE @resultQ4 sql_variant;  DECLARE @Minimum DECIMAL(10,2);  DECLARE @Maximum DECIMAL(10,2);IF object_id('temp_incentive') IS NULL BEGINCREATE TABLE temp_incentive (        type VARCHAR(10),    COMPUTER DECIMAL(10,2),    LAPTOP DECIMAL(10,2),    MOUSE DECIMAL(10,2))            INSERT INTO temp_incentive (type) VALUES ('Median')INSERT INTO temp_incentive (type) VALUES ('FirstQ')INSERT INTO temp_incentive (type) VALUES ('ThirdQ')INSERT INTO temp_incentive (type) VALUES ('Minimum')INSERT INTO temp_incentive (type) VALUES ('Maximum') END  set nocount ON   set @sqlstmt = 'insert #tempmedian select ' + @columnname +      ' from ' + @tablename + ' order by 1 asc '    create table #tempmedian (col sql_variant)  exec (@sqlstmt)  SELECT @rCount= count(*) FROM #tempmedianSELECT @Minimum = Min(convert(DECIMAL(10,2),col)) FROM #tempmedianSELECT @Maximum = max(convert(DECIMAL(10,2),col)) FROM #tempmedian --UPDATE temp_incentive SET @columnname = @Minimum WHERE type = 'Minimum'         SET @stmt = 'update temp_incentive SET '+@columnname+' = '+convert(VARCHAR(max),@Minimum)+' where type = ''Minimum''' ;                EXEC sp_executesql @stmt                SET @stmt = 'update temp_incentive SET '+@columnname+' = '+convert(VARCHAR(max),@Maximum)+' where type = ''Maximum''' ;                EXEC sp_executesql @stmt--median--***************************************************************************    IF (@rCount%2 = 0)     BEGIN        SET @mPos1 = (@rCount/2)        SET @mPos2 = @mPos1+1                -- if @mpos1 & @mpos2 is even         IF(@mPos1%2 =0)                BEGIN                SET @Q1 = (@mPos1/2)        SET @Q2 = @Q1+1                SET @Q3 = (@mPos2+@rCount)/2        SET @Q4 = @Q3+1        --        PRINT '@rCount'--        PRINT @rCount--        PRINT '@mPos1'--        PRINT @mPos1--        PRINT '@mPos2'--        PRINT @mPos2--        PRINT '@Q1'--        PRINT @Q1--        PRINT '@Q2'--        PRINT @Q2--        PRINT '@Q3'--        PRINT @Q3--        PRINT '@Q4'--        PRINT @Q4                declare c_med cursor scroll for select * from #tempmedian           open c_med                fetch absolute @mPos1 from c_med into @result1                 fetch absolute @mPos2 from c_med into @result2                 fetch absolute @Q1 from c_med into @resultQ1                 fetch absolute @Q2 from c_med into @resultQ2                 fetch absolute @Q3 from c_med into @resultQ3                 fetch absolute @Q4 from c_med into @resultQ4           close c_med          deallocate c_med                 SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),(convert(DECIMAL(10,2),@result1)+convert(DECIMAL(10,2),@result2))/2.0)+' where type = ''Median''' ;--        PRINT @stmt        EXEC sp_executesql @stmt                SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),(convert(DECIMAL(10,2),@resultQ1)+convert(DECIMAL(10,2),@resultQ2))/2.0)+' where type = ''FirstQ''' ;--        PRINT @stmt        EXEC sp_executesql @stmt                SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),(convert(DECIMAL(10,2),@resultQ3)+convert(DECIMAL(10,2),@resultQ4))/2.0)+' where type = ''ThirdQ''' ;--        PRINT @stmt        EXEC sp_executesql @stmt                                --        SELECT ((convert(DECIMAL(10,2),@result1)+convert(DECIMAL(10,2),@result2))/2)--        SELECT ((convert(DECIMAL(10,2),@resultQ1)+convert(DECIMAL(10,2),@resultQ2))/2)--        SELECT ((convert(DECIMAL(10,2),@resultQ3)+convert(DECIMAL(10,2),@resultQ4))/2)        END-- if @mpos1 & @mpos2 is odd         ELSE         BEGIN                SET @Q1 = (@mPos1+1)/2        SET @Q2 = @Q1+(@rCount/2)        --        PRINT @mPos1--        PRINT @mPos2--        PRINT @Q1--        PRINT @Q2                declare c_med cursor scroll for select * from #tempmedian           open c_med                fetch absolute @mPos1 from c_med into @result1                 fetch absolute @mPos2 from c_med into @result2                 fetch absolute @Q1 from c_med into @resultQ1                 fetch absolute @Q2 from c_med into @resultQ2           close c_med          deallocate c_med                 SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),(convert(DECIMAL(10,2),@result1)+convert(DECIMAL(10,2),@result2))/2.0)+' where type = ''Median''' ;        EXEC sp_executesql @stmt        SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),convert(DECIMAL(10,2),@resultQ1))+' where type = ''FirstQ''' ;        EXEC sp_executesql @stmt        SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),convert(DECIMAL(10,2),@resultQ2))+' where type = ''ThirdQ''' ;        EXEC sp_executesql @stmt--        SELECT ((convert(DECIMAL(10,2),@result1)+convert(DECIMAL(10,2),@result2))/2)--        SELECT ((convert(DECIMAL(10,2),@resultQ1)))--        SELECT ((convert(DECIMAL(10,2),@resultQ2)))        END    END            --***************************************************************************************--***************************************************************************************-- Median when count is odd    ELSE     BEGIN                SET @mPos1 = (@rCount+1)/2        SET @mPos2 = @mPos1+1        -- if @mpos1 & @mpos2 is odd         IF(@mPos1%2 = 1)                BEGIN                SET @Q1 = (@mPos1/2)        SET @Q2 = @Q1+1                SET @Q3 = (@mPos2+@rCount)/2        SET @Q4 = @Q3+1                --        PRINT '@rCount'--        PRINT @rCount--        PRINT '@mPos1'--        PRINT @mPos1--        PRINT '@Q1'--        PRINT @Q1--        PRINT '@Q2'--        PRINT @Q2--        PRINT '@Q3'--        PRINT @Q3--        PRINT '@Q4'--        PRINT @Q4                declare c_med cursor scroll for select * from #tempmedian           open c_med                fetch absolute @mPos1 from c_med into @result1                                 fetch absolute @Q1 from c_med into @resultQ1                 fetch absolute @Q2 from c_med into @resultQ2                 fetch absolute @Q3 from c_med into @resultQ3                 fetch absolute @Q4 from c_med into @resultQ4           close c_med          deallocate c_med                 SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),convert(DECIMAL(10,2),@result1))+' where type = ''Median''' ;        EXEC sp_executesql @stmt                SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),(convert(DECIMAL(10,2),@resultQ1)+convert(DECIMAL(10,2),@resultQ2))/2.0)+' where type = ''FirstQ''' ;        EXEC sp_executesql @stmt        SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),(convert(DECIMAL(10,2),@resultQ3)+convert(DECIMAL(10,2),@resultQ4))/2.0)+' where type = ''ThirdQ''' ;        EXEC sp_executesql @stmt--        SELECT ((convert(DECIMAL(10,2),@result1)))--        SELECT ((convert(DECIMAL(10,2),@resultQ1)+convert(DECIMAL(10,2),@resultQ2))/2)--        SELECT ((convert(DECIMAL(10,2),@resultQ3)+convert(DECIMAL(10,2),@resultQ4))/2)        END-- if @mpos1 & @mpos2 is even         ELSE         BEGIN                SET @Q1 = (@mPos1+1)/2        SET @Q2 = (@Q1+(@rCount/2))+1        --        PRINT @mPos1--        PRINT @mPos2--        PRINT @Q1--        PRINT @Q2                declare c_med cursor scroll for select * from #tempmedian           open c_med                fetch absolute @mPos1 from c_med into @result1                 fetch absolute @mPos2 from c_med into @result2                 fetch absolute @Q1 from c_med into @resultQ1                 fetch absolute @Q2 from c_med into @resultQ2           close c_med          deallocate c_med                 SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),(convert(DECIMAL(10,2),@result1)+convert(DECIMAL(10,2),@result2))/2.0)+' where type = ''Median''' ;        EXEC sp_executesql @stmt        SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),convert(DECIMAL(10,2),@resultQ1))+' where type = ''FirstQ''' ;        EXEC sp_executesql @stmt        SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),convert(DECIMAL(10,2),@resultQ2))+' where type = ''ThirdQ''' ;        EXEC sp_executesql @stmt--        SELECT ((convert(DECIMAL(10,2),@result1)+convert(DECIMAL(10,2),@result2))/2)--        SELECT ((convert(DECIMAL(10,2),@resultQ1)))--        SELECT ((convert(DECIMAL(10,2),@resultQ2)))        END    END    --********************************************************ENDGO--*********************** Test Data ***********************IF OBJECT_ID ('dbo.TEST_MEDIAN') IS NOT NULL    DROP TABLE dbo.TEST_MEDIANGOCREATE TABLE dbo.TEST_MEDIAN    (    NAME     VARCHAR (50) NOT NULL,    COMPUTER INT NOT NULL,    LAPTOP   INT NOT NULL,    MOUSE    INT NOT NULL,    ID       INT IDENTITY NOT NULL    )GOINSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)VALUES ('Document Control', 150, 78, 65)GOINSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)VALUES ('Engineering', 100, 89, 26)GOINSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)VALUES ('Executive', 75, 45, 75)GOINSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)VALUES ('Facilities and Maintenance', 90, 65, 45)GOINSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)VALUES ('Finance', 45, 32, 43)GOINSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)VALUES ('Human Resources', 62, 25, 73)GOINSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)VALUES ('Information Services', 85, 68, 91)GOINSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)VALUES ('Marketing', 95, 98, 82)GOINSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)VALUES ('Production', 45, 52, 93)GOINSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)VALUES ('Production Control', 32, 56, 95)GOINSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)VALUES ('Purchasing', 85, 45, 46)GOINSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)VALUES ('Quality Assurance', 63, 75, 49)GOINSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)VALUES ('Research and Development', 84, 53, 76)GOINSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)VALUES ('Sales', 75, 32, 61)GOINSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)VALUES ('Shipping and Receiving', 12, 62, 65)GOINSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)VALUES ('Tool Design', 96, 85, 67)GOINSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)VALUES ('Control', 200, 178, 165)GO-- ********************* Execute SP ***********************DECLARE @result sql_variant EXEC dbo.sp_calc_median 'dbo.test_median', 'COMPUTER', @result OUTPUT DECLARE @result1 sql_variant EXEC dbo.sp_calc_median 'dbo.test_median', 'LAPTOP', @result1 OUTPUT DECLARE @result2 sql_variant EXEC dbo.sp_calc_median 'dbo.test_median', 'MOUSE', @result2 OUTPUT SELECT * FROM temp_incentive DROP TABLE temp_incentive

 Wednesday, November 30, 2011 - 8:57:52 AM - David Morton Back To Top (15250) Brahm:  You are absolutely correct - the order by in the following line must be the field that contains the values we want to calculate our median on.  Ordinal = ROW_NUMBER() OVER (ORDER BY FieldName) By using this technique our concerns for ordering the data values has been met.    Here is the first example (the cusor one)  re-coded using Scott's approach and I apologize for not using a CTE to begin with.   --drop table TestTable--go CREATE TABLE [dbo].[TestTable]( [id] [int] IDENTITY(1,1) NOT NULL, [testID] [int] NULL, [testName] [varchar](50) NULL, [testDate] [datetime] NULL, CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED ([id] ASC) )-- sample dataINSERT INTO TestTable (testID, testName, testDate)SELECT 75, 'Dave', '2000-11-01' UNIONSELECT 55, 'Mike', '1995-01-11' UNIONSELECT 35, 'Sue' , '1965-07-14' UNIONSELECT 80, 'Jill', '2001-03-07' --UNION --SELECT 90, 'Abe' , '2005-09-13' ; WITH Domain AS (     SELECT  TestId, TestName, TestDate,        Ordinal = ROW_NUMBER() OVER (ORDER BY TestId)            FROM dbo.TestTable            WHERE TestId IS NOT NULL),Tally AS (      SELECT  Number = COUNT(*)      FROM Domain)SELECT  Median = AVG(TestId)FROM Domain, TallyWHERE Ordinal IN (Number / 2 + 1, (Number + 1) / 2)  -- Query 2SELECT  TestId, TestName, TestDate,        ROW_NUMBER() OVER (ORDER BY TestId)  as Ordinal        FROM dbo.TestTable            WHERE TestId IS NOT NULL If you look at the result of the second query, you will see that the data is actually sorted without the use of an additional sort clause.  The Row_Number() Function has done that for us.   Thanks guys - your all steeley eyed rocket scientists!   Dave Morton

 Wednesday, November 30, 2011 - 12:37:45 AM - BrahmB Back To Top (15242) Just one thing  - remember that one must sort the results on the field that one calculates the MEDIAN on. SQL 2005 and further does not allow one to ORDER BY on derived tables and views. Will play with your suggested solutions. regards Brahm

 Wednesday, November 30, 2011 - 12:29:33 AM - BrahmB Back To Top (15241) Hi Flemming Thor Hansen  and Scott C This is really helpful - about 2 and a half years ago I did the Median with Temp Tables, where I calculate the median over different categories. I even started to think of rather converting it to a .NET assembly. Your solutions really addresses the requirement regarding the even and uneven row counts. Great solutions.  Scott C - Your last query is to to point - have not seen anything like this yet. Just one word to both of you guys -WOW! Kind regards Brahm

 Tuesday, November 29, 2011 - 12:28:13 PM - David Morton Back To Top (15240) Wow!  Nice code examples!  I have to agree that the CTE is much better than using cursors.  Cursors are my bane and slowly I'm coming around to using CTE's, but it's a slow process for me! :)    Thanks for the updated code.   Dave

 Tuesday, November 29, 2011 - 11:41:13 AM - Scott C Back To Top (15238) I agree that it is much better to solve this problem without resorting to temp tables or cursors.  I have some minor quibbles however. Most aggregate functions should explicitly exclude NULL values. The main query is repeated in the SELECT COUNT(*) subquery, which could be ***bersome if it is more complex than a simple one-table SELECT.  Using common table expressions avoids having to repeat the main query. This query has the same execution plan as the previous one, the only difference is style. `WITH Domain AS (    SELECT  FieldName,            Ordinal = ROW_NUMBER() OVER (ORDER BY FieldName)    FROM dbo.TableName    WHERE FieldName IS NOT NULL),Tally AS (    SELECT  Number = COUNT(*)    FROM Domain)SELECT  Median = AVG(FieldName)FROM Domain, TallyWHERE Ordinal IN (Number / 2 + 1, (Number + 1) / 2)`

 Tuesday, November 8, 2011 - 3:08:12 AM - Flemming Thor Hansen Back To Top (15045) Much faster and direct method without cursors.  -- Calculate MEDIAN of FieldName in Table TableName   SELECT AVG(FieldName) AS Median FROM   ( SELECT    FieldName,                    ROW_NUMBER() OVER ( ORDER BY FieldName ASC ) AS [FieldNameRank],                    ( SELECT    COUNT(*)                      FROM      TableName                    ) AS [FieldNameCount]          FROM      TableName        ) AS MT WHERE  [FieldNameRank] IN ( [FieldNameCount] / 2 + 1, ( [FieldNameCount] + 1 ) / 2 )