How to find Mean or Median value

Last Post 24 Jan 2006 10:55 PM by SwePeso. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Shibly
New Member
New Member

--
07 Jan 2003 08:31 AM
Can anyone help to find Mean or Median value through SQL Server.
I see a Median function but it says 'Median' is not a recognized function name.
Thanks
satya
New Member
New Member

--
07 Jan 2003 08:39 AM
BOL refers If the respective values in the Sales measure for the USA, CANADA and MEXICO members are 1000, 2000, and 3000, the following example returns 2000:

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

HTH
Shibly
New Member
New Member

--
07 Jan 2003 09:30 AM
Once you run Median function SQL Server says Median is not a recognized function
BobBarker
New Member
New Member

--
07 Jan 2003 09:38 AM
Mean is easy. An arithmatic mean is the same as an average. So use the AVERAGE aggregate function

SELECT AVERAGE(InvoiceTotal) FROM Orders

Median is a bit harder. The median is the value that lies in the center of the distribution. If there are 100 Orders records, and you sort them in order of InvoiceTotal, the median is found at the 50th record.

I don't know why they don't have a built-in aggregate function for this, since it seems like it would be an easy thing for the database engine to do.

Here's one way to get the median:

BEGIN

SELECT Identity(int, 1,1) AS Id, InvoiceTotal
INTO #Temp
FROM Orders
ORDER BY InvoiceTotal

SELECT @Median = InvoiceTotal FROM #Temp WHERE Id = ((SELECT Count(*) FROM #Temp) /2)

DROP TABLE #Temp

RETURN @Median
END


BB
ScottPletcher
New Member
New Member

--
07 Jan 2003 11:03 AM
Bob,
Technically I think the median of 100 records is the average of the 50th and 51st records; the median of 99 records would be record 50

Shibly,
Although cursors should generally be avoided, I think a scrollable cursor is the best option for median. For example:

DECLARE medianCsr SCROLL CURSOR FOR
SELECT anyCol
FROM anyTable
ORDER BY anyCol
FOR READ ONLY
DECLARE @rows INT
DECLARE @medianRow INT
DECLARE @median DECIMAL(10,2) --for example; change as needed
DECLARE @medianHold DECIMAL(10,2) --for example; change as needed

OPEN medianCsr
SET @rows = @@CURSOR_ROWS
IF @rows % 2 = 1
BEGIN
--odd # of rows, get middle row = median
SET @medianRow = @rows / 2
FETCH ABSOLUTE @medianRow FROM medianCsr INTO @median
END --IF
ELSE
BEGIN
--even # of rows, get middle two rows and average (if numeric column)
SET @medianRow = @rows / 2
FETCH ABSOLUTE @medianRow FROM medianCsr INTO @medianHold
SET @rows = @rows + 1
FETCH ABSOLUTE @medianRow FROM medianCsr INTO @median
SET @median = (@median + @medianHold) / 2
END --ELSE
SELECT @median
SwePeso
New Member
New Member

--
24 Jan 2006 10:55 PM
That is because MEDIAN function is only available in Analysis Server
You are not authorized to post a reply.

Acceptable Use Policy