INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Jobs

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

CODE

         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

CODE

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

My Archive

Resources

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close