Hitesh Goyal Hitesh Goyal - 4 months ago 27
SQL Question

calculating median over a column using SQL

I have a sample table named Table1. In the table there are two columns ID and Age. I want to calculate median over age.
For this i have put on following SQL query

SELECT Age as Median FROM
(SELECT a1.Age, COUNT(a1.Age) Rank
FROM Table1 a1, Table1 a2
WHERE a1.Age < a2.Age OR a1.Age=a2.Age
group by a1.Age
order by a1.Age desc) a3
WHERE Rank = (SELECT (COUNT(*)+1) DIV 2 FROM Table1);

But i am getting syntax error

syntax error in query expression Rank = (SELECT (COUNT(*)+1) DIV 2 FROM Table1)

Please tell where in i am missing


I don't think MS Access SQL has a DIV operator.

I think you need to workaround it using / 2 (divide by 2 resulting in a double float) or * 0.5 (multiple by half, resulting in a fixed point decimal) then taking the integer part e.g. using INT(), noting it always rounds down so that INT(0.9) == 0 and INT(-0.1) == -1 but since the COUNT set function (cardinality) cannot return a negative value you should get the expected result e.g.

SELECT INT( ( COUNT(*) + 1 ) / 2 ) FROM Table1