Median of GROUP BY values

[jimfo...@compumarc.com]

In

http://groups-beta.google.com/group/comp.databases.ms-access/msg/46197725b88fc3fd?hl=en

I said:

If qryRankForMedian is changed to select only values within a group
(along with a suitable WHERE clause for WantRanking) it may be possible
to get the median of each group in its output line as well by using SQL
to reference the Groups value. To do that for the Median function
would require modification to accept the Groups value as an argument.
Of course the odds of someone needing this functionality are long. If
anyone needs that I'll try to flesh out the details.

I received an email from someone in Europe requesting that I provide
the details of how to do this. I wonder how many people are reading
this NG?

tblSample:
ID Value Groups
1 1 A
2 2 A
3 3 A
4 4 B
5 5 B
6 5 B
7 5 C
8 6 C

qryRankForMedian:
SELECT tblSample.Value, (SELECT Count(A.ID) FROM tblSample AS A WHERE
A.Value < tblSample.Value AND A.Groups = tblSample.Groups)+(SELECT
Count(A.ID) FROM tblSample AS A WHERE A.Value = tblSample.Value AND
A.ID < tblSample.ID AND A.Groups = tblSample.Groups)+1 AS
RankingWithinGroup, (SELECT Count(*) FROM tblSample AS A WHERE
A.Groups = tblSample.Groups)/2+0.5 AS WantRankingWithinGroup, Groups
FROM tblSample;

! qryRankForMedian:
Value RankingWithinGroup WantRankingWithinGroup Groups
1 1 2 A
2 2 2 A
3 3 2 A
4 1 2 B
5 2 2 B
5 3 2 B
5 1 1.5 C
6 2 1.5 C

qryGroupMedians:
SELECT Avg(Value) AS Median, qryRankForMedian.Groups FROM
qryRankForMedian WHERE (Abs([RankingWithinGroup] -
[WantRankingWithinGroup]) < 0.6) GROUP BY qryRankForMedian.Groups;

! qryGroupMedians:
Median Groups
2 A
5 B
5.5 C

These are the only values I tested so be sure to test thoroughly before
using this technique. I will be getting a new email address soon so in
the meantime please post any questions or comments to this NG.

James A. Fortune