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

How in the SQL do I get these two statements into one?

P: n/a
I'm trying to find the median of a set of records.

My table is creatively named Table1 and my Column (Field) is named Variable.
This is what I've come up with so far:

Query 1:

SELECT Min(Variable) FROM Table1

WHERE Variable IN

(SELECT TOP 50 PERCENT Variable FROM Table1 ORDER BY Variable DESC)

UNION

SELECT Max(Variable) FROM Table1

WHERE Variable IN (SELECT TOP 50 PERCENT Variable FROM Table1 ORDER BY
Variable ASC);

Query 2:

SELECT AVG(Variable) AS MEDIAN FROM MyTable

How do I incorporate the second statement into the first, so that I don't
have to run two queries?

I've tried following some median code examples on the Web but they seem
either useless or too complicated for me to grasp

Tom
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Tue, 20 Apr 2004 19:00:49 +0200, "Tom Farmen" <to**@iot.ntnu.no>
wrote:

I actually like your implementation. Even takes care of the situation
where there is no single median value so you need to get the average
of the closest two.

Wouldn't this be the only query to run:
SELECT AVG(Variable) AS MEDIAN FROM Query1

Under the hood, Access will then execute Query1.

-Tom.
I'm trying to find the median of a set of records.

My table is creatively named Table1 and my Column (Field) is named Variable.
This is what I've come up with so far:

Query 1:

SELECT Min(Variable) FROM Table1

WHERE Variable IN

(SELECT TOP 50 PERCENT Variable FROM Table1 ORDER BY Variable DESC)

UNION

SELECT Max(Variable) FROM Table1

WHERE Variable IN (SELECT TOP 50 PERCENT Variable FROM Table1 ORDER BY
Variable ASC);

Query 2:

SELECT AVG(Variable) AS MEDIAN FROM MyTable

How do I incorporate the second statement into the first, so that I don't
have to run two queries?

I've tried following some median code examples on the Web but they seem
either useless or too complicated for me to grasp

Tom


Nov 12 '05 #2

P: n/a
Of course! Thank you very much for pointing this out.
I guess I was too tired from hours trying to work out the first part that I
didn't see the obvious.
You saved my day !

Tom
"Tom van Stiphout" <to*****@no.spam.cox.net> wrote in message
news:bu********************************@4ax.com...
On Tue, 20 Apr 2004 19:00:49 +0200, "Tom Farmen" <to**@iot.ntnu.no>
wrote:

I actually like your implementation. Even takes care of the situation
where there is no single median value so you need to get the average
of the closest two.

Wouldn't this be the only query to run:
SELECT AVG(Variable) AS MEDIAN FROM Query1

Under the hood, Access will then execute Query1.

-Tom.
I'm trying to find the median of a set of records.

My table is creatively named Table1 and my Column (Field) is named Variable.This is what I've come up with so far:

Query 1:

SELECT Min(Variable) FROM Table1

WHERE Variable IN

(SELECT TOP 50 PERCENT Variable FROM Table1 ORDER BY Variable DESC)

UNION

SELECT Max(Variable) FROM Table1

WHERE Variable IN (SELECT TOP 50 PERCENT Variable FROM Table1 ORDER BY
Variable ASC);

Query 2:

SELECT AVG(Variable) AS MEDIAN FROM MyTable

How do I incorporate the second statement into the first, so that I don't
have to run two queries?

I've tried following some median code examples on the Web but they seem
either useless or too complicated for me to grasp

Tom

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.