SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Statistics in SQL (Sample Chapter)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 05/27/2002 :  11:40:15  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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

USA
7423 Posts

Posted - 11/08/2002 :  15:42:25  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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.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.


Go to Top of Page

kbiel
Starting Member

2 Posts

Posted - 07/01/2005 :  16:33:22  Show Profile  Reply with Quote
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.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.
Go to Top of Page

kbiel
Starting Member

2 Posts

Posted - 07/01/2005 :  17:52:38  Show Profile  Reply with Quote
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 non-unique 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)

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000