Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Script to calculate the Median value for SQL Server data

MSSQLTips author Eli Leiba By:   |   Read Comments (14)   |   Related Tips: More > T-SQL
Problem

The standard SQL language has a number of aggregate functions like: SUM, MIN, MAX, AVG, but a common statistics function that SQL Server does not have is a built-in aggregate function for median. The median is the value that falls in the middle of a sorted resultset with equal parts that are smaller and equal parts that are greater. Since there is no built-in implementation for the median, the following is a simple solution I put together to find the median.

Solution

The general solution I'm suggesting here uses a stored procedure I created called dbo.sp_calc_median.  The need I had was to just find the median value for various resultsets, so I put together this stored procedure that could be used for just about anything.  The procedure takes a table name and a column name to calculate the median value for any data type.

The procedure builds an ordered temporary table of the column values and by using a dynamic scrollable cursor, scrolls to the middle of the result set and outputs the result.

Here is the stored procedure:

create procedure dbo.sp_calc_median
 (@tablename varchar(50),
  @columnname varchar(50),
  @result sql_variant OUTPUT)
as
begin
  declare @sqlstmt varchar(200)
  declare @midCount int
  set nocount on
  set @sqlstmt = 'insert #tempmedian select ' + @columnname +
      ' from ' + @tablename + ' order by 1 asc '
  create table #tempmedian (col sql_variant)
  exec (@sqlstmt)
  declare c_med cursor scroll for select * from #tempmedian
  select @midCount = round ( count(*) * 0.5,0 ) from #tempmedian
  open c_med
        fetch absolute @midCount from c_med into @result
  close c_med
  deallocate c_med
  drop table #tempmedian
end
go

Here is small sample data set and sample executions, so it is easy to see that this is working.

--sample table
CREATE TABLE [dbo].[TestTable](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [testID] [int] NULL,
 [testName] [varchar](50) NULL,
 [testDate] [date] NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
([id] ASC) )
-- sample data
INSERT INTO TestTable (testID, testName, testDate)
SELECT 1, 'Dave', '2000-11-01' UNION
SELECT 2, 'Mike', '1995-01-11' UNION
SELECT 3, 'Sue' , '1965-07-14' UNION
SELECT 4, 'Jill', '2001-03-07' UNION
SELECT 5, 'Abe' , '2005-09-13'
-- sample run
DECLARE @result sql_variant 
EXEC dbo.sp_calc_median 'dbo.TestTable', 'testID', @result OUTPUT
SELECT @result
EXEC dbo.sp_calc_median 'dbo.TestTable', 'testName', @result OUTPUT
SELECT @result
EXEC dbo.sp_calc_median 'dbo.TestTable', 'testDate', @result OUTPUT
SELECT @result
-- output from above run
3
Jill
2000-11-01

With this small dataset it is easy to see how these values are determined as shown below where the values are sorted.

  • testID - 1, 2, 3, 4, 5
  • testName - Abe, Dave, Jill, Mike, Sue
  • testDate - 1965-07-14, 1995-01-11, 2000-11-01, 2001-03-07, 2005-09-13

Here is another sample run I did against the Northwind database. This table has more data than the sample above, but works just the same. Also note that I am passing in the database name along with the table, so this stored procedure could be created in one database on your SQL Server instance and used for any database and table.

DECLARE @res sql_variant -- declaring a sql_variant column
EXEC sp_calc_median 'northwind..products','productname',@res OUTPUT 
PRINT convert(nvarchar,@res) 
EXEC sp_calc_median 'northwind..products','UnitPrice',@res OUTPUT
PRINT convert(real,@res) 
-- output from above run  
Maxilaku
19.5
Next Steps
  • Create the sp_calc_median stored procedure and test against your data.
  • Look at other ways you could use this in your environment.
  • There are several other ways to determine the median value, but this was one simple solution that satisfied my needs.  Hopefully this solution gets the wheels turning.
  • Check out these related tips


Last Update: 10/31/2011


About the author
MSSQLTips author Eli Leiba
Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Monday, October 31, 2011 - 8:26:21 AM - BJ Read The Tip

Thanks.  This is a simple option.


Monday, October 31, 2011 - 11:23:04 AM - David Morton Read The Tip

This is a nifty piece of code but I can see one small issue with it.  It works great for odd numbered sets of data but in the case of even numbered sets of data, you would want to take the two middle values and average them together.  All said, if you don't need that level of granularity, then this works great.

In the perfect world, if we had an even number set of data and the two center values were different, such as a set of data that has 50 elements:

row 25:    50.00

row 26:    70.00

The actual median value would be (50 + 70) / 2 = 60.

If the set of data had 49 elements then the median value would have been the 25th element or 50.

Here is an example - I used a #tmp1 table to store my intermediate results.  I could have used the ID Column because it exists but what if we didn't have an ID table or the values where not linear?  Remember, Medians are calculated on an ordered set of values regarless of their order in the parent table.  I also used the original code and modified the TestId values.

CREATE TABLE [dbo].[TestTable](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [testID] [int] NULL,
 [testName] [varchar](50) NULL,
 [testDate] [datetime] NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED

([id] ASC) )
-- sample data
INSERT INTO TestTable (testID, testName, testDate)
SELECT 35, 'Dave', '2000-11-01' UNION
SELECT 55, 'Mike', '1995-01-11' UNION
SELECT 75, 'Sue' , '1965-07-14' UNION
SELECT 80, 'Jill', '2001-03-07'

UNION
SELECT 90, 'Abe' , '2005-09-13'


-- sample run
Declare @cntr2 int
declare @cntr3 int

 Select
 *
 , Row_Number() over (order by TestId) as RowNo
 into #tmp1
 from TestTable
 

Select @cntr = count(*) from TestTable

Select @cntr2 = round ( count(*) * 0.5,0 ) from testtable

set @cntr3 = @cntr2 + 1
 
if @cntr % 2 = 0
BEGIN
 Select
  TestId, TestName, TestDate
 from #tmp1
 where RowNo in(@cntr2, @cntr3)
 
 Select
  Avg(TestId) as Median
 from #tmp1
 where RowNo in(@cntr2, @cntr3)
end
else
begin
 Select
  *
 from #tmp1
 where RowNo = @cntr2
End

 

--***** Play with this.  Try commenting out the last Union and Insert Statement to create an even number set of data and you will see what

-- I am talking about.

 

Cheers

 


Monday, October 31, 2011 - 11:37:37 AM - Jeremy Kadlec Read The Tip

David,

Thank you for the post with the example code and data.

Thank you,
Jeremy Kadlec


Tuesday, November 08, 2011 - 12:26:16 AM - Brahm Read The Tip

Hi Good and effectve solution.

 

Just not that the code will work correctly if count(*) is uneven.

Remember that one has to get the average between the  middle two terms if count(*) is even


Tuesday, November 08, 2011 - 12:27:30 AM - Brahm Read The Tip

Thanks David Morton for your solution.

 

 


Tuesday, November 08, 2011 - 3:08:12 AM - Flemming Thor Hansen Read The Tip

Much faster and direct method without cursors.

 -- Calculate MEDIAN of FieldName in Table TableName
 
 
 SELECT AVG(FieldName) AS Median
 FROM   ( SELECT    FieldName,
                    ROW_NUMBER() OVER ( ORDER BY FieldName ASC ) AS [FieldNameRank],
                    ( SELECT    COUNT(*)
                      FROM      TableName
                    ) AS [FieldNameCount]
          FROM      TableName
        ) AS MT
 WHERE  [FieldNameRank] IN ( [FieldNameCount] / 2 + 1, ( [FieldNameCount] + 1 ) / 2 )


Tuesday, November 29, 2011 - 11:41:13 AM - Scott C Read The Tip

I agree that it is much better to solve this problem without resorting to temp tables or cursors.  I have some minor quibbles however.

Most aggregate functions should explicitly exclude NULL values.

The main query is repeated in the SELECT COUNT(*) subquery, which could be ***bersome if it is more complex than a simple one-table SELECT.  Using common table expressions avoids having to repeat the main query.

This query has the same execution plan as the previous one, the only difference is style.

WITH Domain AS (
    SELECT  FieldName,
            Ordinal = ROW_NUMBER() OVER (ORDER BY FieldName)
    FROM dbo.TableName
    WHERE FieldName IS NOT NULL),
Tally AS (
    SELECT  Number = COUNT(*)
    FROM Domain)
SELECT  Median = AVG(FieldName)
FROM Domain, Tally
WHERE Ordinal IN (Number / 2 + 1, (Number + 1) / 2)

Tuesday, November 29, 2011 - 12:28:13 PM - David Morton Read The Tip

Wow!  Nice code examples!  I have to agree that the CTE is much better than using cursors.  Cursors are my bane and slowly I'm coming around to using CTE's, but it's a slow process for me! :) 

 

Thanks for the updated code.

 

Dave

 

 


Wednesday, November 30, 2011 - 12:29:33 AM - BrahmB Read The Tip

Hi Flemming Thor Hansen  and Scott C

This is really helpful - about 2 and a half years ago I did the Median with Temp Tables, where I calculate the median over different categories.

I even started to think of rather converting it to a .NET assembly.

Your solutions really addresses the requirement regarding the even and uneven row counts.

Great solutions.  Scott C - Your last query is to to point - have not seen anything like this yet.

Just one word to both of you guys -WOW!

Kind regards

Brahm


Wednesday, November 30, 2011 - 12:37:45 AM - BrahmB Read The Tip

Just one thing  - remember that one must sort the results on the field that one calculates the MEDIAN on.

SQL 2005 and further does not allow one to ORDER BY on derived tables and views.

Will play with your suggested solutions.

regards

Brahm

 


Wednesday, November 30, 2011 - 8:57:52 AM - David Morton Read The Tip

Brahm:

 You are absolutely correct - the order by in the following line must be the field that contains the values we want to calculate our median on. 

Ordinal = ROW_NUMBER() OVER (ORDER BY FieldName)

By using this technique our concerns for ordering the data values has been met. 

 

Here is the first example (the cusor one)  re-coded using Scott's approach and I apologize for not using a CTE to begin with.

 

--drop table TestTable
--go

CREATE TABLE [dbo].[TestTable](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [testID] [int] NULL,
 [testName] [varchar](50) NULL,
 [testDate] [datetime] NULL,
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
 
([id] ASC) )
-- sample data
INSERT INTO TestTable (testID, testName, testDate)
SELECT 75, 'Dave', '2000-11-01' UNION
SELECT 55, 'Mike', '1995-01-11' UNION
SELECT 35, 'Sue' , '1965-07-14' UNION
SELECT 80, 'Jill', '2001-03-07'
 
--UNION
--SELECT 90, 'Abe' , '2005-09-13'

; WITH Domain AS
(   
 SELECT  TestId, TestName, TestDate,
        Ordinal = ROW_NUMBER() OVER (ORDER BY TestId)   
        FROM dbo.TestTable   
        WHERE TestId IS NOT NULL
),Tally AS
(   
  SELECT  Number = COUNT(*)   
  FROM Domain
)
SELECT  Median = AVG(TestId)
FROM Domain, Tally
WHERE Ordinal IN (Number / 2 + 1, (Number + 1) / 2)

 -- Query 2
SELECT  TestId, TestName, TestDate,
        ROW_NUMBER() OVER (ORDER BY TestId)  as Ordinal
        FROM dbo.TestTable   
        WHERE TestId IS NOT NULL

If you look at the result of the second query, you will see that the data is actually sorted without the use of an additional sort clause.  The Row_Number() Function has done that for us.

 

Thanks guys - your all steeley eyed rocket scientists!

 

Dave Morton

 


Friday, July 13, 2012 - 7:01:10 AM - Anal Patel Read The Tip

--** Procedure to Find Median,1st Quartile,3rd Quartile,Minimum and Maximum
ALTER procedure dbo.sp_calc_median
 (@tablename varchar(50),
  @columnname varchar(50),
  @result sql_variant OUTPUT)
as
BEGIN 
  declare @sqlstmt varchar(200);
  declare @stmt nvarchar(max);
  DECLARE @rCount INT;
  DECLARE @mPos1 INT;
  DECLARE @mPos2 INT;
  DECLARE @Q1 INT;
  DECLARE @Q2 INT;
  DECLARE @Q3 INT;
  DECLARE @Q4 INT;
  DECLARE @result1 SQL_VARIANT;
  DECLARE @result2 sql_variant;
  DECLARE @resultQ1 sql_variant;
  DECLARE @resultQ2 sql_variant;
  DECLARE @resultQ3 sql_variant;
  DECLARE @resultQ4 sql_variant;
  DECLARE @Minimum DECIMAL(10,2);
  DECLARE @Maximum DECIMAL(10,2);

IF object_id('temp_incentive') IS NULL
BEGIN
CREATE TABLE temp_incentive
(   
    type VARCHAR(10),
    COMPUTER DECIMAL(10,2),
    LAPTOP DECIMAL(10,2),
    MOUSE DECIMAL(10,2)
)
           
INSERT INTO temp_incentive (type) VALUES ('Median')
INSERT INTO temp_incentive (type) VALUES ('FirstQ')
INSERT INTO temp_incentive (type) VALUES ('ThirdQ')
INSERT INTO temp_incentive (type) VALUES ('Minimum')
INSERT INTO temp_incentive (type) VALUES ('Maximum')
END

  set nocount ON
  set @sqlstmt = 'insert #tempmedian select ' + @columnname +
      ' from ' + @tablename + ' order by 1 asc '
 
  create table #tempmedian (col sql_variant)
  exec (@sqlstmt)
 
SELECT @rCount= count(*) FROM #tempmedian
SELECT @Minimum = Min(convert(DECIMAL(10,2),col)) FROM #tempmedian
SELECT @Maximum = max(convert(DECIMAL(10,2),col)) FROM #tempmedian

--UPDATE temp_incentive SET @columnname = @Minimum WHERE type = 'Minimum'

        SET @stmt = 'update temp_incentive SET '+@columnname+' = '+convert(VARCHAR(max),@Minimum)+' where type = ''Minimum''' ;       
        EXEC sp_executesql @stmt
       
        SET @stmt = 'update temp_incentive SET '+@columnname+' = '+convert(VARCHAR(max),@Maximum)+' where type = ''Maximum''' ;       
        EXEC sp_executesql @stmt

--median
--***************************************************************************
    IF (@rCount%2 = 0)
    BEGIN
        SET @mPos1 = (@rCount/2)
        SET @mPos2 = @mPos1+1
               
-- if @mpos1 & @mpos2 is even

        IF(@mPos1%2 =0)       
        BEGIN
       
        SET @Q1 = (@mPos1/2)
        SET @Q2 = @Q1+1
       
        SET @Q3 = (@mPos2+@rCount)/2
        SET @Q4 = @Q3+1
       
--        PRINT '@rCount'
--        PRINT @rCount
--        PRINT '@mPos1'
--        PRINT @mPos1
--        PRINT '@mPos2'
--        PRINT @mPos2
--        PRINT '@Q1'
--        PRINT @Q1
--        PRINT '@Q2'
--        PRINT @Q2
--        PRINT '@Q3'
--        PRINT @Q3
--        PRINT '@Q4'
--        PRINT @Q4
       
        declare c_med cursor scroll for select * from #tempmedian
          open c_med
                fetch absolute @mPos1 from c_med into @result1
                fetch absolute @mPos2 from c_med into @result2
                fetch absolute @Q1 from c_med into @resultQ1
                fetch absolute @Q2 from c_med into @resultQ2
                fetch absolute @Q3 from c_med into @resultQ3
                fetch absolute @Q4 from c_med into @resultQ4
          close c_med
          deallocate c_med
       
        SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),(convert(DECIMAL(10,2),@result1)+convert(DECIMAL(10,2),@result2))/2.0)+' where type = ''Median''' ;
--        PRINT @stmt
        EXEC sp_executesql @stmt
       
        SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),(convert(DECIMAL(10,2),@resultQ1)+convert(DECIMAL(10,2),@resultQ2))/2.0)+' where type = ''FirstQ''' ;
--        PRINT @stmt
        EXEC sp_executesql @stmt
       
        SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),(convert(DECIMAL(10,2),@resultQ3)+convert(DECIMAL(10,2),@resultQ4))/2.0)+' where type = ''ThirdQ''' ;
--        PRINT @stmt
        EXEC sp_executesql @stmt
                               
--        SELECT ((convert(DECIMAL(10,2),@result1)+convert(DECIMAL(10,2),@result2))/2)
--        SELECT ((convert(DECIMAL(10,2),@resultQ1)+convert(DECIMAL(10,2),@resultQ2))/2)
--        SELECT ((convert(DECIMAL(10,2),@resultQ3)+convert(DECIMAL(10,2),@resultQ4))/2)
        END

-- if @mpos1 & @mpos2 is odd

        ELSE
        BEGIN
       
        SET @Q1 = (@mPos1+1)/2
        SET @Q2 = @Q1+(@rCount/2)
       
--        PRINT @mPos1
--        PRINT @mPos2
--        PRINT @Q1
--        PRINT @Q2
       
        declare c_med cursor scroll for select * from #tempmedian
          open c_med
                fetch absolute @mPos1 from c_med into @result1
                fetch absolute @mPos2 from c_med into @result2
                fetch absolute @Q1 from c_med into @resultQ1
                fetch absolute @Q2 from c_med into @resultQ2
          close c_med
          deallocate c_med
       
        SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),(convert(DECIMAL(10,2),@result1)+convert(DECIMAL(10,2),@result2))/2.0)+' where type = ''Median''' ;
        EXEC sp_executesql @stmt

        SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),convert(DECIMAL(10,2),@resultQ1))+' where type = ''FirstQ''' ;
        EXEC sp_executesql @stmt

        SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),convert(DECIMAL(10,2),@resultQ2))+' where type = ''ThirdQ''' ;
        EXEC sp_executesql @stmt

--        SELECT ((convert(DECIMAL(10,2),@result1)+convert(DECIMAL(10,2),@result2))/2)
--        SELECT ((convert(DECIMAL(10,2),@resultQ1)))
--        SELECT ((convert(DECIMAL(10,2),@resultQ2)))
        END

    END   
       
--***************************************************************************************
--***************************************************************************************
-- Median when count is odd

    ELSE
    BEGIN
       
        SET @mPos1 = (@rCount+1)/2
        SET @mPos2 = @mPos1+1       
-- if @mpos1 & @mpos2 is odd

        IF(@mPos1%2 = 1)       
        BEGIN
       
        SET @Q1 = (@mPos1/2)
        SET @Q2 = @Q1+1
       
        SET @Q3 = (@mPos2+@rCount)/2
        SET @Q4 = @Q3+1       
       
--        PRINT '@rCount'
--        PRINT @rCount
--        PRINT '@mPos1'
--        PRINT @mPos1
--        PRINT '@Q1'
--        PRINT @Q1
--        PRINT '@Q2'
--        PRINT @Q2
--        PRINT '@Q3'
--        PRINT @Q3
--        PRINT '@Q4'
--        PRINT @Q4
       
        declare c_med cursor scroll for select * from #tempmedian
          open c_med
                fetch absolute @mPos1 from c_med into @result1                
                fetch absolute @Q1 from c_med into @resultQ1
                fetch absolute @Q2 from c_med into @resultQ2
                fetch absolute @Q3 from c_med into @resultQ3
                fetch absolute @Q4 from c_med into @resultQ4
          close c_med
          deallocate c_med
       
        SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),convert(DECIMAL(10,2),@result1))+' where type = ''Median''' ;
        EXEC sp_executesql @stmt
       
        SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),(convert(DECIMAL(10,2),@resultQ1)+convert(DECIMAL(10,2),@resultQ2))/2.0)+' where type = ''FirstQ''' ;
        EXEC sp_executesql @stmt

        SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),(convert(DECIMAL(10,2),@resultQ3)+convert(DECIMAL(10,2),@resultQ4))/2.0)+' where type = ''ThirdQ''' ;
        EXEC sp_executesql @stmt

--        SELECT ((convert(DECIMAL(10,2),@result1)))
--        SELECT ((convert(DECIMAL(10,2),@resultQ1)+convert(DECIMAL(10,2),@resultQ2))/2)
--        SELECT ((convert(DECIMAL(10,2),@resultQ3)+convert(DECIMAL(10,2),@resultQ4))/2)

        END

-- if @mpos1 & @mpos2 is even

        ELSE
        BEGIN
       
        SET @Q1 = (@mPos1+1)/2
        SET @Q2 = (@Q1+(@rCount/2))+1
       
--        PRINT @mPos1
--        PRINT @mPos2
--        PRINT @Q1
--        PRINT @Q2
       
        declare c_med cursor scroll for select * from #tempmedian
          open c_med
                fetch absolute @mPos1 from c_med into @result1
                fetch absolute @mPos2 from c_med into @result2
                fetch absolute @Q1 from c_med into @resultQ1
                fetch absolute @Q2 from c_med into @resultQ2
          close c_med
          deallocate c_med
       
        SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),(convert(DECIMAL(10,2),@result1)+convert(DECIMAL(10,2),@result2))/2.0)+' where type = ''Median''' ;
        EXEC sp_executesql @stmt

        SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),convert(DECIMAL(10,2),@resultQ1))+' where type = ''FirstQ''' ;
        EXEC sp_executesql @stmt

        SET @stmt = 'update temp_incentive SET ' + quotename(@columnname) +' = '+convert(VARCHAR(max),convert(DECIMAL(10,2),@resultQ2))+' where type = ''ThirdQ''' ;
        EXEC sp_executesql @stmt

--        SELECT ((convert(DECIMAL(10,2),@result1)+convert(DECIMAL(10,2),@result2))/2)
--        SELECT ((convert(DECIMAL(10,2),@resultQ1)))
--        SELECT ((convert(DECIMAL(10,2),@resultQ2)))
        END
    END   

--********************************************************

END
GO



--*********************** Test Data ***********************

IF OBJECT_ID ('dbo.TEST_MEDIAN') IS NOT NULL
    DROP TABLE dbo.TEST_MEDIAN
GO

CREATE TABLE dbo.TEST_MEDIAN
    (
    NAME     VARCHAR (50) NOT NULL,
    COMPUTER INT NOT NULL,
    LAPTOP   INT NOT NULL,
    MOUSE    INT NOT NULL,
    ID       INT IDENTITY NOT NULL
    )
GO



INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Document Control', 150, 78, 65)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Engineering', 100, 89, 26)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Executive', 75, 45, 75)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Facilities and Maintenance', 90, 65, 45)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Finance', 45, 32, 43)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Human Resources', 62, 25, 73)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Information Services', 85, 68, 91)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Marketing', 95, 98, 82)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Production', 45, 52, 93)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Production Control', 32, 56, 95)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Purchasing', 85, 45, 46)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Quality Assurance', 63, 75, 49)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Research and Development', 84, 53, 76)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Sales', 75, 32, 61)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Shipping and Receiving', 12, 62, 65)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Tool Design', 96, 85, 67)
GO

INSERT INTO dbo.TEST_MEDIAN (NAME, COMPUTER, LAPTOP, MOUSE)
VALUES ('Control', 200, 178, 165)
GO



-- ********************* Execute SP ***********************

DECLARE @result sql_variant
EXEC dbo.sp_calc_median 'dbo.test_median', 'COMPUTER', @result OUTPUT

DECLARE @result1 sql_variant
EXEC dbo.sp_calc_median 'dbo.test_median', 'LAPTOP', @result1 OUTPUT

DECLARE @result2 sql_variant
EXEC dbo.sp_calc_median 'dbo.test_median', 'MOUSE', @result2 OUTPUT

SELECT * FROM temp_incentive

DROP TABLE temp_incentive



Friday, February 01, 2013 - 8:45:27 PM - Bhavesh Read The Tip

Hi Anal, 

  Can we convert this into a function, do you have any suggestions to add 5 and 95 percentiles. 

This is kind of urgent

 

Thanks,

Bhavesh


Tuesday, July 30, 2013 - 9:27:01 AM - Jerry Day Read The Tip

select avg (vch) from
(
(SELECT MAX(testid) AS vch FROM (SELECT TOP(50) PERCENT testid FROM TestTable ORDER BY testid ASC)  A) 
UNION ALL
(SELECT MIN(testid) as vch FROM (SELECT TOP(50) PERCENT testid FROM TestTable ORDER BY testid DESC) B)
)  X
 

 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.