Sql Server 7.0/Access 2000 median calculation 1

[niteflite]

How can i get the median value of a column? for example i want the median of the price column for all rows. the average, count, sum, etc are easy to get. how about the median?

thanks.

 

[RiverGuy]

I don't know of a median function. If you added a rowtotal column to your table, you could do it in a stored procedure. Something like


CREATE PROCEDURE spMedian AS

set nocount on

declare @rowTotal numeric
declare @rowCount numeric
declare @midPoint numeric

set @rowTotal = 1

drop table tblTemp

SELECT *
into tblTemp
FROM (SELECT TOP 100 PERCENT *
FROM tblMedian
ORDER BY Amt) DERIVEDTBL


update tblTemp set @rowTotal = [RowTot] = @rowTotal + [RowTot]

select @rowCount = (sum([RowTot] - 1)) FROM tblTemp

set @midPoint = @rowCount / 2

select * from tblTemp where RowTot = @midPoint

GO


You would want the default value for the RowTotal column to be 1.

 

[RiverGuy]

Oops. That code is way wrong. Dunno what I was thinking. Here is some thats better:

[\b]
CREATE PROCEDURE spMedian AS

set nocount on

declare @rowTotal numeric


set @rowTotal = 1

drop table tblTemp

SELECT *
into tblTemp
FROM (SELECT TOP 100 PERCENT *
FROM tblMedian
ORDER BY Amt) DERIVEDTBL


update tblTemp set @rowTotal = [RowTot] = @rowTotal + [RowTot]




select * from tblTemp where RowTot = convert(bigint, ((@rowTotal/2) + 1))
GO
[\b]

I don't know if medians are supposed to round up or down if there is an even amount, so I didn't make any provisions for that.

 

[cheyney]

how about this? just a select statement, no procedure:

select top 1 A.price
from
(select top 50 percent B.price
from tblWhatever B
order by B.price ASC) as A
order by A.price DESC

like riverguy, i don't know whether to go up or down in the case of an even amount.

cheyney

 

[RiverGuy]

Nice one, Cheyney. Less overhead is always the best way!

 

[cheyney]

ack, it seems for a true mathematical median you go halfway inbetween the two middle numbers if there's an even number of elements. This complicates things a little, but no big deal. You can get the count and test whether its even or odd with modular division by 2

if its odd, use whats above. If its even, use whats above to get the "lesser" middle number, then switch the ASC and DESC to get the "greater" middle number. Then find the average of them.

if you're doing this is a stored procedure, something like this (assume table name is tblWhatever) should suffice:

DECLARE @count int
DECLARE @median money

select @median = top 1 A.price
from
(select top 50 percent B.price
from tblWhatever B
order by B.price ASC) as A
order by A.price DESC

select @count = count(*)
from tblWhatever
if @count % 2 = 0
-- even count, median = average of two center numbers
begin
DECLARE @upper money
select @upper = top 1 A.price
from
(select top 50 percent B.price
from tblWhatever B
order by B.price DESC) as A
order by A.price ASC

select @median = (@median + @upper) / 2
end

print @median

 

[RiverGuy]

I wonder if there is any special provisions in a median if there are 2 or more middle elements. Meaning, if 100 should be the median, and they are 2 or more records with 100, what then?

It's interesting stuff. The times I may have messed with finding results of special funtions like this, I've just relied on reports. Interesting when it needs to be accomplished via SQL.

 

[cheyney]

median is just the middle value of a list

so if the list is

3,15,72,101,20003

then the median is 72

if there is one more number in the list, like this:

3,15,72,101,20003,100000005

then the median is halfway between 72 and 101, i.e. 86.5

These are the only two cases (even and odd numbers of elements), except of course if the list is empty.

I'm not sure what you mean by your post, riverguy

cheyney

 

[RiverGuy]

I'm saying you get your median value. It comes up as 100. You notice that there is another record, either before or after, with the same median value of 100. If you want to know who represents the median value, do you disply one of the records, or both of them?

I know its a non-factor if you're only getting the value, but what if you want to find who's recrod is the median record based on one field?

 

[r937]

there's a really great discussion of the sql required to do a median calculation here --

Chapter 8
Statistics in SQL
Transact-SQL Cookbook
O'Reilly

http://www.oreilly.com/catalog/transqlcook/chapter/ch08.html

(yes, the entire chapter is online)

see "Calculating a Median" about halfway down

rudy

http://rudy.ca/

 

[cheyney]

oh i see what you're saying. yeah, not sure how that would work. And how about the case when none of the records have the median value as their price...

 

[fluteplr]

Just for everyones information.

Joe Celko devotes most of a chapter to this problem in his SQL for Smarties book, you may want to have a look.

 

[IBACFII]

Hey, Just a thought. Could I make this into my own UDF and define my table as needed?? example

CREATE Function udfGetMedian (@Table as VarChar(50), @Value as Char(10))
Returns varchar(15)
as
Begin


DECLARE @count int
DECLARE @median Decimal(13,3)

select @median = top 1 A.@Value
from
(select top 50 percent B.@Value
from @Table B
order by B.@Value ASC) as A
order by A.@Value DESC

select @count = count(*)
from @Table
if @count % 2 = 0
-- even count, median = average of two center numbers
begin
DECLARE @upper Decimal(13,3)
select @upper = top 1 A.@Value
from
(select top 50 percent B.@Value
from @Table B
order by B.@Value DESC) as A
order by A.@Value ASC

select @median = (@median + @upper) / 2
end

Return @median
END

YES? NO?
Right now I get some errors like
Incorrect syntax near the keyword 'top'.
any ideas??

 

[r937]

instead of

select @median = top 1 A.@Value

you need to say

select top 1 @median = A.@Value

rudy

http://r937.com/

SQL Consulting

 

[IBACFII]

This is a really wierd problem. ok, I am trying this from a differnet angle how about this

CREATE Function udfGetMedian (@Table as VarChar(50), @Value as varChar(10), @WhereCrit as VarChar(50))
Returns varchar(15)
as
Begin

--Declare @Table as VarChar(50)
--Declare @Value as varChar(10)
--Declare @WhereCrit as VarChar(50)

--set @Table='dbo.tzH01'
--Set @Value='Qty'
--Set @WhereCrit='Where Clientid='+char(39)+'CCH01'+ char(39)

DECLARE @count int
DECLARE @median Decimal(13,3)
Declare @Str1 as VarChar(500)
Declare @Str2 as VarChar(500)

Set @Str1='Select Top 1 '
+@Value
+ ' From ( Select Top 50 percent ' + @Value +
' From '+ @table + ' '
+ @WhereCrit
+ ' Order by ' + @Value
+ ' ) a Order by '+@Value +' DESC'

Set @Str2='Select Top 1 '
+ @Value
+' From ( Select Top 50 percent '
+ @Value
+ ' From '+ @table + ' '
+ @WhereCrit
+ ' Order by ' + @Value
+ ' DESC) a Order by ' + @Value + ' ASC'
Select @Str1
Select @Str2

Exec(@Str1)

Set @Count= select count(*)
from @table

if @count % 2 = 0
Begin
DECLARE @upper Decimal(13,3)
Set @Upper = Exec(@Str2)
set @median = (@median + @upper) / 2
End

Return @median
END

BTW it dosent work... lol.. I cannt seem to set a variable to a select statment

Thanks in advance
SC

 

[SQLSister]

From BOL
The types of statements that are valid in a function include:

DECLARE statements can be used to define data variables and cursors that are local to the function.

Assignments of values to objects local to the function, such as using SET to assign values to scalar and table local variables.

Cursor operations that reference local cursors that are declared, opened, closed, and deallocated in the function. FETCH statements that return data to the client are not allowed. Only FETCH statements that assign values to local variables using the INTO clause are allowed.

Control-of-flow statements.

SELECT statements containing select lists with expressions that assign values to variables that are local to the function.

UPDATE, INSERT, and DELETE statements modifying table variables that are local to the function.

EXECUTE statements calling an extended stored procedure.

I take this to mean that exec statments calling dynamic SQL are not allowed in UDFs

Questions about posting. See faq183-874

 

[IBACFII]

Can I do this is a USP??