Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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)
 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  
 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.04 seconds. Powered By: Snitz Forums 2000