Calculating a median

[Marco Shaw]

It is possible to write a select statement that will give me the median of
some values when my data looks like this:

UserA 10
UserA 20
UserA 30
UserB 40
UserB 50
UserB 60
....
UserN ##

I found this online:
http://databases.aspfaq.com/database/how-do-i-calculate-the-median-in-a-table.html

And tried something like this, but I don't think I'm right:

SELECT User,TOP 1 Value FROM
(
SELECT User,TOP 50 PERCENT Value
FROM Table GROUP BY User
)
GROUP BY User

Anyone ever tried anything similar?

My script seems to complain about this part above "TOP 1 Value". Something
about a missing operator. In a case like this, should I be using some kind
of delimiter for that part?

 

[John Spencer]

TOP n should be placed Immediately before any field names. That said, could
you be more specific in what you want? Do you want the median value for
each user or for the entire data set. Also given the set

User A with values of 10, 20, 30, 40. What is the median? (is it the
average of the two values 20 and 30 or is it 20 or is it 30?)

To get a value for ONE specific user, you could use
SELECT TOP 1 User, Value
FROM
(SELECT TOP 50 PerCent User, Value
FROM TheTable
WHERE User = "UserA"
ORDER BY Value DESC)

 

[Marco Shaw]

TOP n should be placed Immediately before any field names. That said,
could you be more specific in what you want? Do you want the median value
for each user or for the entire data set. Also given the set

I want the median for each user.

User A with values of 10, 20, 30, 40. What is the median? (is it the
average of the two values 20 and 30 or is it 20 or is it 30?)

To get a value for ONE specific user, you could use
SELECT TOP 1 User, Value
FROM
(SELECT TOP 50 PerCent User, Value
FROM TheTable
WHERE User = "UserA"
ORDER BY Value DESC)

I'll give that a try... Might cause me problems if I have, say, pair or
non-pair # of records for a user?

Marco

 

[Jeff Boyce]

From statistics, the median of an odd number of elements is the middle one
(ascending or descending order, no matter). For an even number of elements,
order them first, then average the two in the middle.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/