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  Site Related Forums  Article Discussion  Article: Statistics in SQL (Sample Chapter)

Author  Topic

0 Posts

 Posted - 2002-05-27 : 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, easy-to-use 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

7423 Posts

 Posted - 2002-11-08 : 15:42:25 Here's any easier way to get a median (not the financial median, however):SELECT x.Value AS medianFROM Vals AS x, Vals AS yGROUP BY x.ValueHAVING SUM(SIGN(x.Value-y.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.Value-y.Value)) IN (1,0,-1))That should do the trick. Let me know what y'all think.

kbiel
Starting Member

2 Posts

 Posted - 2005-07-01 : 16:33:22 quote:Originally posted by jsmith8858Here's any easier way to get a median (not the financial median, however):SELECT x.Value AS medianFROM Vals AS x, Vals AS yGROUP BY x.ValueHAVING SUM(SIGN(x.Value-y.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 non-unique values approaching the median.

kbiel
Starting Member

2 Posts

 Posted - 2005-07-01 : 17:52:38 quote:Originally posted by kbielquote:Originally posted by jsmith8858That should do the trick. Let me know what y'all think.It's a neat trick, but it does not work for non-unique values approaching the median.This should work:`DECLARE @Vals TABLE ( Value INT, i INT identity(1,1) )INSERT INTO @ValsSELECT SomeValueColumnFROM SomeTableORDER BY SomeValueColumn ASCSELECT AVG(Value)FROM @ValsWHERE 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)`