Median - SQL Server Forums

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)

Median

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-19 : 09:29:28

writes "How to find a Median Value for a set of data. There is no good example to use it in books online. Could any help me out."

ToddV
Posting Yak Master

218 Posts

Posted - 2002-02-19 : 10:15:23

First of all, I always get condused what a median is, but I know it is one of these.

Second of all it seems like someone whould have a better way to do this, but...


Create Table ##tempTodd (
	Value INT)

INSERT ##tempTodd Values(1)
INSERT ##tempTodd Values(1)
INSERT ##tempTodd Values(1)
INSERT ##tempTodd Values(2)
INSERT ##tempTodd Values(3)

DECLARE @SQL Varchar(1000),
	@MiddleRowNbr INT,
	@MidleDistinctValueRowNbr INT

SELECT @MiddleRowNbr = Ceiling(Count(Value)/2.0),
	@MidleDistinctValueRowNbr = Ceiling(Count(Distinct Value)/2.0)
FROM ##tempTodd

SET @SQL = 'SELECT TOP 1 Value
		FROM (SELECT TOP ' + LTRIM(@MiddleRowNbr) + 'Value
			FROM ##tempTodd
			ORDER BY Value ASC)AS A
		Order by Value desc'
EXEC (@SQL)

SET @SQL = 'SELECT TOP 1 Value
		FROM (SELECT DISTINCT TOP ' + LTRIM(@MidleDistinctValueRowNbr) + 'Value
			FROM ##tempTodd
			ORDER BY Value ASC)AS A
		Order by Value desc'
EXEC (@SQL)

izaltsman
A custom title

1139 Posts

Posted - 2002-02-19 : 11:04:12

If you want to take a look at some more in-depth discussion of calculating Median via T-SQL, you might want to invest in Ken Henderson's book, called "The Guru's Guide to Transact-SQL". It has a very good section dedicated specifically to this subject. It also has a ton of other extremely useful info.

You can find this book here:

http://sqlteam.com/store.asp

ToddV
Posting Yak Master

218 Posts

Posted - 2002-02-19 : 11:17:55

I should have known to go there. DAH!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources