×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Microsoft SQL Server: Programming FAQ

T-SQL Hints and Tips

Calculating a median by JohnDTampaBay
Posted: 21 Jan 04 (Edited 23 Jan 04)

Calculating a median value is a simple concept to the complex human mind. Simply put, the median value is the middle value in a list of ordered values, with the same number of values above it as below it.

In a result set that contains 5 rows, row 3 will contain the median value (Example 1). When the result set contains an even number of rows, the median is calculated by averaging the two middle rows (Example 2).

Example 1 û Median=7          Example 2 û Median=5

CODE

1                      1
2                      2

CODE

7                      3

8

CODE

7

CODE

9                      8
9

The list of values must be ordered or the median calculated will be a random value. The difficulty for SQL Server developers lies in the fact that there is no order to SQL Server rows. This makes medians cumbersome to code.

The solution takes a few steps to pull off:
• Create a holding table with an IDENTITY column to hold the ordered data
• INSERT the data into the holding table using an ORDER BY clause
• Capture the ROWCOUNT of the INSERT
• Do a little math to determine which row(s) you need to look at
• Use the IDENTITY column of the temp table to reference the appropriate rows and calculate the median
This is an example of how to calculate the median price in a table of prices:

CODE

DECLARE @Median decimal(9,4)
, @mid int, @odd int, @mid1 int, @mid2 int
, @NumRows  int

CREATE TABLE #Prices (
ID int IDENTITY(1,1)
, Price decimal(9,4)
)

INSERT INTO #Prices (Price)
SELECT Price
FROM PriceTable

ORDER BY Price

CODE

SET @NumRows  = @@ROWCOUNT
SET @odd = @NumRows  % 2 --1 if odd number of rows, 0 if even
SET @mid = @NumRows  / 2 --Number of the "middle" record

IF @NumRows  = 1 BEGIN   --Special trick so this works if only one record
SET @odd = 1
SET @mid = 1
END

-- Which row(s) hold the median value?
IF @odd = 1 BEGIN
--Odd number of rows - median is the middle one
SET @Median = (SELECT Price FROM #Prices WHERE ID = @mid)
END ELSE BEGIN
--Even number of rows - median is the average of the two middle ones
SET @mid1 = (SELECT Number FROM #Prices WHERE ID = @mid)
SET @mid2 = (SELECT Number FROM #Prices WHERE ID = @mid + 1)
SET @Median = (@mid1 + @mid2) / 2
END

SELECT @Median

If, for instance, you have a table of prices by age and want to calculate the median price by age, you can wrap the above logic in a cursor to loop through the ages and calculate the median.

[color maroon]

CODE

--Holding table for median rows
CREATE TABLE #Prices (
ID int IDENTITY(1,1)
, Price decimal(9,4)
)

DECLARE @Age int, @Median decimal(9,4)
, @mid int, @odd int, @mid1 int, @mid2 int
, @NumRows  int

DECLARE oCursor CURSOR FOR
SELECT DISTINCT Age
FROM PricesByAge
ORDER BY Age ASC

--Open said cursor and start stepping thru it
OPEN oCursor

WHILE 0=0 BEGIN
FETCH NEXT FROM oCursor INTO @Age
IF @@Fetch_Status<>0 BREAK

TRUNCATE TABLE #Prices

[color red]

CODE

--Insert the data for a given age
INSERT INTO #Prices (Price)
SELECT Price
FROM PricesByAge
WHERE Age = @Age
ORDER BY Price

--Now actually compute the median
SET @NumRows  = @@ROWCOUNT
SET @odd = @NumRows  % 2        --1 if odd number of recs, 0 if even
SET @mid = @NumRows  / 2        --Number of the "middle" record

IF @NumRows  = 1 BEGIN            --Special trick so this works if only one record
SET @odd = 1
SET @mid = 1
END

-- Which row(s) hold the median value?
IF @odd = 1 BEGIN
--Odd number of rows - median is the middle one
SET @Median = (SELECT Price FROM #Prices WHERE ID = @mid)
END ELSE BEGIN
--Even number of rows - median is the average of the two middle ones
SET @mid1 = (SELECT Price FROM #Prices WHERE ID = @mid)
SET @mid2 = (SELECT Price FROM #Prices WHERE ID = @mid + 1)
SET @Median = (@mid1 + @mid2) / 2
END

SELECT @Age AS Age, @Median AS MedianPrice

CODE

END

CLOSE oCursor
DEALLOCATE oCursor

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!