Script to calculate the Median value for SQL Server data

By:   |   Updated: 2011-10-31   |   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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2011-10-31

Comments For This Article




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 Maximum
ALTER procedure dbo.sp_calc_median
 (@tablename varchar(50),
  @columnname varchar(50),
  @result sql_variant OUTPUT)
as
BEGIN 
  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
BEGIN
CREATE 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 #tempmedian
SELECT @Minimum = Min(convert(DECIMAL(10,2),col)) FROM #tempmedian
SELECT @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   

--********************************************************

END
GO



--*********************** Test Data ***********************

IF OBJECT_ID ('dbo.TEST_MEDIAN') IS NOT NULL
    DROP TABLE dbo.TEST_MEDIAN
GO

CREATE 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
    )
GO



INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Document Control', 150, 78, 65)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Engineering', 100, 89, 26)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Executive', 75, 45, 75)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Facilities and Maintenance', 90, 65, 45)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Finance', 45, 32, 43)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Human Resources', 62, 25, 73)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Information Services', 85, 68, 91)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Marketing', 95, 98, 82)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Production', 45, 52, 93)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Production Control', 32, 56, 95)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Purchasing', 85, 45, 46)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Quality Assurance', 63, 75, 49)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Research and Development', 84, 53, 76)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Sales', 75, 32, 61)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Shipping and Receiving', 12, 62, 65)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Tool Design', 96, 85, 67)
GO

INSERT 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 data
INSERT INTO TestTable (testID, testName, testDate)
SELECT 75, 'Dave', '2000-11-01' UNION
SELECT 55, 'Mike', '1995-01-11' UNION
SELECT 35, 'Sue' , '1965-07-14' UNION
SELECT 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, Tally
WHERE Ordinal IN (Number / 2 + 1, (Number + 1) / 2)

 -- Query 2
SELECT  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, Tally
WHERE 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 )

Tuesday, November 8, 2011 - 12:27:30 AM - Brahm Back To Top (15044)

Thanks David Morton for your solution.

 

 

Tuesday, November 8, 2011 - 12:26:16 AM - Brahm Back To Top (15043)

Hi Good and effectve solution.

 

Just not that the code will work correctly if count(*) is uneven.

Remember that one has to get the average between the  middle two terms if count(*) is even

Monday, October 31, 2011 - 11:37:37 AM - Jeremy Kadlec Back To Top (14968)

David,

Thank you for the post with the example code and data.

Thank you,
Jeremy Kadlec

Monday, October 31, 2011 - 11:23:04 AM - David Morton Back To Top (14967)

This is a nifty piece of code but I can see one small issue with it.  It works great for odd numbered sets of data but in the case of even numbered sets of data, you would want to take the two middle values and average them together.  All said, if you don't need that level of granularity, then this works great.

In the perfect world, if we had an even number set of data and the two center values were different, such as a set of data that has 50 elements:

row 25:    50.00

row 26:    70.00

The actual median value would be (50 + 70) / 2 = 60.

If the set of data had 49 elements then the median value would have been the 25th element or 50.

Here is an example - I used a #tmp1 table to store my intermediate results.  I could have used the ID Column because it exists but what if we didn't have an ID table or the values where not linear?  Remember, Medians are calculated on an ordered set of values regarless of their order in the parent table.  I also used the original code and modified the TestId values.

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 data
INSERT INTO TestTable (testID, testName, testDate)
SELECT 35, 'Dave', '2000-11-01' UNION
SELECT 55, 'Mike', '1995-01-11' UNION
SELECT 75, 'Sue' , '1965-07-14' UNION
SELECT 80, 'Jill', '2001-03-07'

UNION
SELECT 90, 'Abe' , '2005-09-13'


-- sample run
Declare @cntr2 int
declare @cntr3 int

 Select
 *
 , Row_Number() over (order by TestId) as RowNo
 into #tmp1
 from TestTable
 

Select @cntr = count(*) from TestTable

Select @cntr2 = round ( count(*) * 0.5,0 ) from testtable

set @cntr3 = @cntr2 + 1
 
if @cntr % 2 = 0
BEGIN
 Select
  TestId, TestName, TestDate
 from #tmp1
 where RowNo in(@cntr2, @cntr3)
 
 Select
  Avg(TestId) as Median
 from #tmp1
 where RowNo in(@cntr2, @cntr3)
end
else
begin
 Select
  *
 from #tmp1
 where RowNo = @cntr2
End

 

--***** Play with this.  Try commenting out the last Union and Insert Statement to create an even number set of data and you will see what

-- I am talking about.

 

Cheers

 

Monday, October 31, 2011 - 8:26:21 AM - BJ Back To Top (14965)

Thanks.  This is a simple option.














get free sql tips
agree to terms