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 without use of Cursors by hmckillop
Posted: 22 Jan 04 (Edited 5 Feb 04)

This is a different version to AngelWPBTampa's article on how to find the median of a column, I have added how to do the samething without a cursor.
-- I have now also added a procedure version which accepts two parameters tablename and column name for which you want to find the median off (see bottom)
Cursors can prove to be very inefficient if incorrectly used so take some time to analyse Angel's solution and determine for yourself using profiling which solution would best suit your own needs.
For this example I use a table named aa_testtable containing two rows, OID (unique identifier) and TestValue both ints. The process is simple, determine the middle row i.e. rowcount/2,
If odd number of records then get the value from that middle ordered row,
otherwise (if even ) take the average from the two middle rows.

--Declare variables to hold the positions and results
Declare @v_rowcount bigint
DECLARE @v_odd int
DECLARE @v_mid int
declare @v_result1 decimal
declare @v_result2 decimal

--set the rowcount to be the total number of rows in your table
select @v_rowcount = count(OID) from aa_testtable

SET @v_odd = @v_rowcount % 2        --1 if odd number of recs, 0 if even
SET @v_mid = (@v_rowcount / 2)+1        --Number of the "middle" record

select @v_mid as MiddleRow

set rowcount  @v_mid

select @v_result1 = TestValue from aa_testtable order by testvalue

if @v_odd = 0
begin
    set @v_mid = @v_mid -1 --get the previous row
    set rowcount  @v_mid
    select @v_result2 = TestValue from aa_testtable order by testvalue
    set @v_result1 = (@v_result1 + @v_result2)/2
end

--the current value of @v_result1 is the median of column testvalue
select @v_result1



this is a proc version to find the median and allows two parameters of the tablename and fieldname and will return the median of this field in the table. Note no checks take place to ensure the correct data type etc.



CREATE PROC usp_CalcMedian
(
    @pi_TableName VARCHAR(255),
    @pi_MedianRow VARCHAR(255)
)
AS
BEGIN
    Declare @v_rowcount bigint
    DECLARE @v_odd int
    DECLARE @v_mid int
    declare @v_result1 decimal
    declare @v_result2 decimal
    DECLARE @v_SQL nvarchar(500)
    DECLARE @ParmDefinition NVARCHAR(500)
    
    --set the rowcount to be the total number of rows in your table
    SET @v_SQL = 'select @v_rowcountOUT = count(*) from ' + @pi_TableName
    SET @ParmDefinition = N'@v_rowcountOUT int OUTPUT'
    exec sp_executesql @v_SQL, @ParmDefinition, @v_rowcountOUT= @v_rowcount OUTPUT

    SET @v_odd = @v_rowcount % 2        --1 if odd number of recs, 0 if even
    SET @v_mid = (@v_rowcount / 2)+1        --Number of the "middle" record
    
    select @v_mid as MiddleRow
    
    set rowcount  @v_mid
    
    SET @v_SQL = 'select @v_result1OUT = ' + @pi_MedianRow + ' from ' + @pi_TableName + ' order by ' + @pi_MedianRow
    SET @ParmDefinition = N'@v_result1OUT int OUTPUT'
    exec sp_executesql @v_SQL, @ParmDefinition, @v_result1OUT= @v_result1 OUTPUT
    
    if @v_odd = 0
    begin
        set @v_mid = @v_mid -1 --get the previous row
        set rowcount  @v_mid
        SET @v_SQL = 'select @v_result2OUT = ' + @pi_MedianRow + ' from ' + @pi_TableName + ' order by ' + @pi_MedianRow
        SET @ParmDefinition = N'@v_result2OUT int OUTPUT'
        exec sp_executesql @v_SQL, @ParmDefinition, @v_result2OUT= @v_result2 OUTPUT
        set @v_result1 = (@v_result1 + @v_result2)/2
    end
    
    --the current value of @v_result1 is the median of column testvalue
    select @v_result1

end

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