Thanks.  This is a simple option.

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

David,

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

Thank you,
Jeremy Kadlec

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

Thanks David Morton for your solution.

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 )

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)

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

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

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

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