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

General SQL Server Forums

New to SQL Server Programming

median

Author

Topic

JJins
Yak Posting Veteran

81 Posts

Posted - 2011-08-31 : 11:14:50

how to I change this to find median?

SELECT AVG(totalpayments)[DE]
FROM BGBtest
where effectivedate between '2011-01-01 00:00:00.000' and

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-08-31 : 11:58:06

median is tricky with sql server. Here's one SwePeso blog about it:
http://weblogs.sqlteam.com/peterl/archive/2009/09/16/Median-and-weighted-median.aspx

Plenty of topics here about "median". Try searching for other solutions as well

Be One with the Optimizer
TG

JJins
Yak Posting Veteran

81 Posts

Posted - 2011-08-31 : 13:35:23

Thanks! so how would I subsitute this if my table is bgbtest and my field is totalpayments.

does the second select match the first? would this work?

SELECT AVG(1.0E * x)
FROM (
SELECT x,
ROW_NUMBER() OVER (ORDER BY x DESC) AS a,
ROW_NUMBER() OVER (ORDER BY x) AS b
FROM @Foo
) AS d
WHERE b - a BETWEEN -1 AND 1

--------

SELECT AVG(1.0E * totalpayments)
From (
select totalpayments,
Row_Number() Over (Order BY totalpayments DESC) as A,
Row_Number() Over (Order BY totalpayments) As b
FROM BGBtest
) AS d
where b-a between -1 and 1 and effectivedate between '2011-01-01 00:00:00.000' and '2011-08-31 00:00:00.000' and type = 'G'

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-08-31 : 13:51:57

seems to equivalent. The best thing would be for you to set up some samples where you know what the answer should be and test your statement.

Be One with the Optimizer
TG

JJins
Yak Posting Veteran

81 Posts

Posted - 2011-08-31 : 14:02:53

what is 1.0E? and is Foo the name of a table?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-31 : 14:56:38

You copied the wrong piece of code. The code you copied was the one I have seen mostly and it has a built-in flaw if some criterias is not followed.

-- Peso Median 
SELECT  AVG(1E * TotalPayments)
FROM    (
            SELECT  TotalPayments,
                    2 * ROW_NUMBER() OVER (ORDER BY TotalPayments) - COUNT(*) OVER () AS y
            FROM    dbo.BGBtest
        ) AS d
WHERE   y BETWEEN 0 AND 2

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources