Latest from MSSQLTips

Free SQL Server Learning

Script to calculate the Median value for SQL Server data

 By: Eli Leiba   |   Read Comments (14)   |   Related Tips: More > T-SQL
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

Last Update: 10/31/2011

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

View all my tips
Related Resources

 Print Tweet Become a paid author

Post a comment or let the author know this tip helped you.

All comments are reviewed, so stay on subject or we may delete your comment.

 *Name *Email Notify for updates

Get free SQL tips:

 *Enter Code

 Tuesday, July 30, 2013 - 9:27:01 AM - Jerry Day Read The Tip 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 01, 2013 - 8:45:27 PM - Bhavesh Read The Tip 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 Read The Tip --** 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 Read The Tip 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 Read The Tip 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 Read The Tip 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 Read The Tip 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 Read The Tip 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 08, 2011 - 3:08:12 AM - Flemming Thor Hansen Read The Tip 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 08, 2011 - 12:27:30 AM - Brahm Read The Tip Thanks David Morton for your solution.

 Tuesday, November 08, 2011 - 12:26:16 AM - Brahm Read The Tip 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 Read The Tip 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 Read The Tip 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 dataINSERT INTO TestTable (testID, testName, testDate)SELECT 35, 'Dave', '2000-11-01' UNIONSELECT 55, 'Mike', '1995-01-11' UNIONSELECT 75, 'Sue' , '1965-07-14' UNIONSELECT 80, 'Jill', '2001-03-07' UNION SELECT 90, 'Abe' , '2005-09-13' -- sample runDeclare @cntr2 intdeclare @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 = 0BEGIN Select  TestId, TestName, TestDate from #tmp1 where RowNo in(@cntr2, @cntr3)  Select  Avg(TestId) as Median from #tmp1 where RowNo in(@cntr2, @cntr3)endelsebegin Select  * from #tmp1 where RowNo = @cntr2End   --***** 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 Read The Tip Thanks.  This is a simple option.

Follow

Get Free SQL Tips

Pinterest

DBAs

Developers

BI Professionals

Careers

Q and A

Today's Tip

Tutorials

Webcasts

Whitepapers

Tools

Tip Categories

Search By TipID

Top Ten

Authors

Community

First Timer?

Pictures

Free T-shirt

Contribute

Events

User Groups

Author of the Year

Join