Author 
Topic 

AskSQLTeam
Ask SQLTeam Question
USA
0 Posts 
Posted  05/27/2002 : 11:40:15

Ales Spetic submitted "While SQL was never designed to be a statistics package, the language is quite versatile and holds a lot of hidden potential when it comes to calculating statistics. If you need a fast, easytouse tool for quick analysis of an existing data set, then the concepts presented in this article may prove quite helpful. The text covers topics like: Means, Modes, Medians Standard deviations, Variances, Standard errors. Confidence intervals, Correlations, Moving averages, Weighted moving averages." Article Link. 

jsmith8858
Dr. Cross Join
USA
7423 Posts 
Posted  11/08/2002 : 15:42:25

Here's any easier way to get a median (not the financial median, however):
SELECT x.Value AS median FROM Vals AS x, Vals AS y GROUP BY x.Value HAVING SUM(SIGN(x.Valuey.Value)) IN (1,0)
Change the " IN (1,0)" to a 1 to get the lower value if there is an even # of values. Currently, the higher # is returned.
Basically, we are saying compare each number to all possible numbers, and get a adds values of 1,0 or 1 if the first number is less, equal or higher than the second. The number that returns 0 is right in the middle ... If there is no middle, a 1 or 1 is returned.
To get the financial median, you need to encapsulate the results of the above into a subquery, allow for not just (1,0) but all three (1,0,1) and then take the AVG of the values returned.
That is,
SELECT Avg(Median) as Median FROM ( SELECT x.Value AS median FROM Vals AS x, Vals AS y GROUP BY x.Value HAVING SUM(SIGN(x.Valuey.Value)) IN (1,0,1) )
That should do the trick. Let me know what y'all think.



kbiel
Starting Member
2 Posts 
Posted  07/01/2005 : 16:33:22

quote: Originally posted by jsmith8858
Here's any easier way to get a median (not the financial median, however):
SELECT x.Value AS median FROM Vals AS x, Vals AS y GROUP BY x.Value HAVING SUM(SIGN(x.Valuey.Value)) IN (1,0)
That should do the trick. Let me know what y'all think.
It's a neat trick, but it does not work for nonunique values approaching the median. 


kbiel
Starting Member
2 Posts 
Posted  07/01/2005 : 17:52:38

quote: Originally posted by kbiel
quote: Originally posted by jsmith8858 That should do the trick. Let me know what y'all think.
It's a neat trick, but it does not work for nonunique values approaching the median.
This should work:
DECLARE @Vals TABLE ( Value INT, i INT identity(1,1) )
INSERT INTO @Vals
SELECT
SomeValueColumn
FROM
SomeTable
ORDER BY
SomeValueColumn ASC
SELECT
AVG(Value)
FROM
@Vals
WHERE
i = (SELECT (MAX(i) + 1) / 2 + (MAX(i) + 1) % 2 FROM @Vals)
OR
i = (SELECT (MAX(i) + 1) / 2 FROM @Vals)
That will select the financial median. To select only the middle upper value on an even number of records, then use the following WHERE clause:
WHERE
i = (SELECT (MAX(i) + 1) / 2 + (MAX(i) + 1) % 2 FROM @Vals)
To select only the middle lower value on an even number of records then use this WHERE clause:
WHERE
i = (SELECT MAX(i) / 2 + MAX(i) % 2 FROM @Vals)




Topic 


