Getting the 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

SQL Server 2000 Forums

Transact-SQL (2000)

Getting the Median?

Author

Topic

Lopaka
Starting Member

48 Posts

Posted - 2006-12-08 : 11:35:35

Is there a function to get the median in SQL?

Robert R. Barnes

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-12-08 : 11:45:02

From BOL:
Median
Returns the median value of a numeric expression evaluated over a set.

Syntax
Median(«Set»[, «Numeric Expression»])

Remarks
The Median function returns the median value of a numeric expression, specified in «Numeric Expression», evaluated over a set specified in «Set».

Example
The following example returns 2000 if respective Sales of the countries are 1000, 2000, and 3000:

Median({USA, CANADA, MEXICO}, Sales)

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-12-08 : 11:55:48

The Median function is an MDX function, not an T-SQL function, so it would only be available if your data is in Analysis Services.

For T-SQL, you would need to construct a query to do it. It wouldn't be hard, but definitely less convenient than a function.

Lopaka
Starting Member

48 Posts

Posted - 2006-12-08 : 12:44:01

So T-SQL does not already have a function, just the analysis services? wonderful...:(

Robert R. Barnes

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-08 : 14:21:40

I wrote a median function here for a while ago. Give Search a try.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-08 : 14:31:18

Found it! This is the sample data

Type of Request	Priority	Submit_time		Close_time       	Num days worked
---------------	--------	-------------------	-------------------	---------------
KCR Request	1		2006-10-03 13:08:10	2006-12-04 10:10:03	2
KCR Request	1		2006-01-03 13:08:10	2006-01-08 10:10:03	5
KCR Request	1		2006-01-03 14:08:10	2006-01-06 13:22:06	3
KCR Request	1		2006-02-03 12:08:10	2006-02-09 10:16:03	6
KCR Request	1		2006-02-08 12:08:10	2006-02-14 10:16:03	6
KCR Request	2		2006-01-05 13:08:11	2006-01-08 10:10:03	5
KCR Request	2		2006-01-07 14:08:13	2006-01-06 13:22:06	3
KCR Request	2		2006-02-03 12:08:15	2006-02-09 10:16:03	6
KCR Request	3		2006-01-03 13:08:10	2006-01-08 10:10:03	5
KCR Request	3		2006-01-03 14:08:10	2006-01-06 13:22:06	3
KCR Request	3		2006-02-03 12:08:10	2006-02-09 10:16:03	6
KCR Request	3		2006-02-08 12:08:10	2006-02-14 10:16:03	6
MPR Request	1		2006-10-03 13:08:10	2006-12-04 10:10:03	2
MPR Request	1		2006-10-03 13:08:10	2006-12-04 10:10:03	2
MPR Request	1		2006-10-03 13:08:10	2006-12-04 10:10:03	2
MPR Request	1		2006-10-03 13:08:10	2006-12-04 10:10:03	2
MPR Request	1		2006-10-03 13:08:10	2006-12-04 10:10:03	2
MPR Request	1		2006-10-03 13:08:10	2006-12-04 10:10:03	2

And this is the function code

CREATE FUNCTION dbo.fnMedian
(
    @TypeOfRequest VARCHAR(20),
    @Priority INT,
    @InputStyle TINYINT
)
RETURNS FLOAT
AS

BEGIN
    DECLARE @Items INT,
            @Low INT,
            @High INT

    DECLARE @Values TABLE (ID INT IDENTITY(0, 1) PRIMARY KEY CLUSTERED, Value INT)

    IF @InputStyle = 0 OR @InputStyle IS NULL  -- Ordinary MEDIAN over TypeOfRequest and Priority
        INSERT    @Values
                  (
                      Value
                  )
        SELECT    Num_of_days_worked
        FROM      MyTable
        WHERE     [Type of Request] = @TypeOfRequest
                  AND Priority = @Priority
        ORDER BY  Num_of_days_worked

    IF @InputStyle = 1                         -- Distinct MEDIAN over TypeOfRequest and Priority
        INSERT           @Values
                         (
                             Value
                         )
        SELECT DISTINCT  Num_of_days_worked
        FROM             MyTable
        WHERE            [Type of Request] = @TypeOfRequest
                         AND Priority = @Priority
        ORDER BY         Num_of_days_worked

    IF @InputStyle = 2                         -- Ordinary MEDIAN over Priority only
        INSERT    @Values
                  (
                      Value
                  )
        SELECT    Num_of_days_worked
        FROM      MyTable
        WHERE     Priority = @Priority
        ORDER BY  Num_of_days_worked

    IF @InputStyle = 3                         -- Distinct MEDIAN over Priority only
        INSERT           @Values
                         (
                             Value
                         )
        SELECT DISTINCT  Num_of_days_worked
        FROM             MyTable
        WHERE            Priority = @Priority
        ORDER BY         Num_of_days_worked

    SELECT @Items = @@ROWCOUNT

    IF @Items % 2 = 0               -- Even number of values
        SELECT @High = @Items / 2,
               @Low = @High - 1
    ELSE                            -- Odd number of values
        SELECT @High = @Items / 2,
               @Low = @High

    RETURN 1.0 * (SELECT AVG(Value) FROM @Values WHERE ID IN (@Low, @High))
END

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources