Median in one fell swoop

[agibsonsw]

Hello again. Access 2007.

I built this SQL to determine a median salary (of which I was quite proud!):

Code: Select all

(SELECT Last(Salary) AS Middles 
FROM (SELECT TOP 50 PERCENT Salary FROM tblExcel ORDER BY Salary ASC) 
ORDER BY Last(Salary)) UNION ALL (SELECT Last(Salary)  AS Middles 
FROM (SELECT TOP 50 PERCENT Salary FROM tblExcel ORDER BY Salary DESC) 
ORDER BY Last(Salary) );

But I also need to average the two return values. Is it possible to wrap this in AVG() without a second query? I've had a few goes but no joy so far.

Andy.

P.S. I'm aware that Access will return duplicate values (i.e. more than 50%) but this shouldn't matter in terms of a median value.

[HansV]

SELECT Avg(Middles) As MedianSalary
FROM (SELECT Max(Salary) AS Middles FROM (SELECT TOP 50 PERCENT Salary FROM tblExcel ORDER BY Salary ASC)) UNION ALL (SELECT Min(Salary) FROM (SELECT TOP 50 PERCENT Salary FROM tblExcel ORDER BY Salary DESC))

[agibsonsw]

SELECT Avg(Middles) As MedianSalary
FROM (SELECT Max(Salary) AS Middles FROM (SELECT TOP 50 PERCENT Salary FROM tblExcel ORDER BY Salary ASC)) UNION ALL (SELECT Min(Salary) FROM (SELECT TOP 50 PERCENT Salary FROM tblExcel ORDER BY Salary DESC))

Thanks again! I'll try this latter but I'm sure it will work . Don't know why I keep reverting to First/Last rather than Min/Max when I know they've caused me problems in the past.

Regards, Andy.

[agibsonsw]

SELECT Avg(Middles) As MedianSalary
FROM (SELECT Max(Salary) AS Middles FROM (SELECT TOP 50 PERCENT Salary FROM tblExcel ORDER BY Salary ASC)) UNION ALL (SELECT Min(Salary) FROM (SELECT TOP 50 PERCENT Salary FROM tblExcel ORDER BY Salary DESC))

Oops, this still gives two figures..

[HansV]

Sorry, incorrect nesting of parentheses.

SELECT Avg(Middles) As MedianSalary
FROM (SELECT Max(Salary) AS Middles FROM (SELECT TOP 50 PERCENT Salary FROM tblExcel ORDER BY Salary ASC) UNION ALL SELECT Min(Salary) FROM (SELECT TOP 50 PERCENT Salary FROM tblExcel ORDER BY Salary DESC))

[John Gray]

You are fortunate you were never exposed to LISP...!

[HansV]

Define: Lisp
Result: Lisp is a speech impediment...