By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,728 Members | 2,490 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,728 IT Pros & Developers. It's quick & easy.

Median of GROUP BY values

P: n/a
In

http://groups-beta.google.com/group/...b88fc3fd?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

Nov 13 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.