This may be IMPOSSIBLE.. To calc the MEDIAN

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

This may be IMPOSSIBLE.. To calc the MEDIAN

Author

Topic

fisherman_jake
Slave to the Almighty Yak

159 Posts

Posted - 2001-10-21 : 21:04:07

G'day, I have a problem that's been pestering me for a while, I may just settle with the solution that I have which includes a TEMP table.

The Problem is to calculate the MEDIAN from a set of VALUES, with the condition that you are NOT ALLOWED to use a TEMP table to formulate your solution, this is because this sproc is being run by several sprocs, which means temp table clashes. Unless there is a way of naming temp tables dynamically. You should be able to apply the result to a variable (i.e. SELECT @MEDIAN = RESULT etc.)

Here's a bit of code to get you started.
(You guys know what to run)

/*
Create Table #Values
(
Value int
)

Insert #Values Values ( 1 )
Insert #Values Values ( 12 )
Insert #Values Values ( 10 )
Insert #Values Values ( 10 )
Insert #Values Values ( 2 )
Insert #Values Values ( 2 )
Insert #Values Values ( 5 )
Insert #Values Values ( 5 )
Insert #Values Values ( 7 )
Insert #Values Values ( 8 )
Insert #Values Values ( 11 )
Insert #Values Values ( 9 )
Insert #Values Values ( 6 )
Insert #Values Values ( 3 )
Insert #Values Values ( 4 )
Insert #Values Values ( 7 )

Drop Table #Values
*/

Declare @Modulo tinyint
Declare @MidCount tinyint

-- Modulo is to determine if the COUNT of the set is ODD (1) or EVEN (0)
Select @Modulo = case When ((Select Count(*) from #Values) % 2) <> 0 then 1 else 0 end

-- MidCount is the MIDDLE number,
-- if Modulo = 0 EVEN:
/*
SELECT AVG(B.VALUE) as MEDIAN
FROM
(
SELECT TOP 2 A.VALUE
FROM
(
SELECT TOP @MidCount VALUE -- INVALID SYNTAX TOP @MidCount
FROM #Values
ORDER BY VALUE ASC
) A
ORDER BY A.VALUE DESC
) B
*/
-- if Modulo = 1:
/*
SELECT Max(A.VALUE) as MEDIAN
FROM
(
SELECT TOP @MidCount VALUE -- INVALID SYNTAX TOP @MidCount
FROM #Values
ORDER BY VALUE ASC
) A
*/

Select @MidCount = Floor((Select Count(*) from #Values) / 2) + 1

Select @Modulo as Modulo
Select @MidCount as MidCount

Select * from #Values order by Value

-- RESULT:
-- MEDIAN = (6 + 7) / 2

If there isn't a solution to this then I'll have to live with the solution that I've got. I've created a temp table with an IDENTITY column, easy enough to get all the values LESS THAN @MidCount and selecting the MAX or AVG(TOP 2) depending on the MODULO.

Good luck.

Master Fisherman

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources