lebeau26
asked on
Calculate Median in Totals Query
Hi,
This sounds like a fairly hard question which I hope someone can help me on. I have a "Totals" query that groups by certain fields and calculates the Avg. The following is the SQL :
SELECT REGION, INDUSTRY, Avg(MEAN) AS AvgOfMEAN
FROM (tbl1 INNER JOIN tbl2 ON tbl1.DT = tbl2.DT) INNER JOIN tblRt ON tbl1.ID = tblRt.ID
WHERE (((REGION)="North America") AND ((INDUSTRY)="Transportatio n"))
GROUP BY REGION, INDUSTRY;
Now instead of Avg(Mean) I want the median of the means. How can i do this? Do I need to write a public function and reference it in the query? That I have never done before. Any help is appreciated. Thanks
D
This sounds like a fairly hard question which I hope someone can help me on. I have a "Totals" query that groups by certain fields and calculates the Avg. The following is the SQL :
SELECT REGION, INDUSTRY, Avg(MEAN) AS AvgOfMEAN
FROM (tbl1 INNER JOIN tbl2 ON tbl1.DT = tbl2.DT) INNER JOIN tblRt ON tbl1.ID = tblRt.ID
WHERE (((REGION)="North America") AND ((INDUSTRY)="Transportatio
GROUP BY REGION, INDUSTRY;
Now instead of Avg(Mean) I want the median of the means. How can i do this? Do I need to write a public function and reference it in the query? That I have never done before. Any help is appreciated. Thanks
D
or perhaps
SELECT Avg(AvgOfMEAN)
FROM (SELECT REGION, INDUSTRY, Avg(MEAN) AS AvgOfMEAN
FROM (tbl1 INNER JOIN tbl2 ON tbl1.DT = tbl2.DT) INNER JOIN tblRt ON tbl1.ID = tblRt.ID
WHERE (((REGION)="North America") AND ((INDUSTRY)="Transportatio n"))
GROUP BY REGION, INDUSTRY) AS qryAvg
SELECT Avg(AvgOfMEAN)
FROM (SELECT REGION, INDUSTRY, Avg(MEAN) AS AvgOfMEAN
FROM (tbl1 INNER JOIN tbl2 ON tbl1.DT = tbl2.DT) INNER JOIN tblRt ON tbl1.ID = tblRt.ID
WHERE (((REGION)="North America") AND ((INDUSTRY)="Transportatio
GROUP BY REGION, INDUSTRY) AS qryAvg
ASKER
Sorry, I basically want the Median of the means instead of the Avg of the means. I do want to group by the region and industry though.
So basically it should look like:
Region Industry Median
America Autos 100
England Autos 50
America Food 90
England Food 10
Does this make more sense?
D
So basically it should look like:
Region Industry Median
America Autos 100
England Autos 50
America Food 90
England Food 10
Does this make more sense?
D
Hi lebeau26,
You will need to use a UDF to get a median, unfortunately...
Regards,
Patrick
You will need to use a UDF to get a median, unfortunately...
Regards,
Patrick
ASKER
Ok, so how would I do this? Again I have never used a UDF in a query before. I usually just reference them in VBA.
Also, any ideas on how to code the median UDF ?
Thanks
D
Also, any ideas on how to code the median UDF ?
Thanks
D
Not sure if you'll be able to get a UDF for an aggregate function of course.
But there are many examples of calculating medians out there..
Something like
http://support.microsoft.com/?kbid=210581
Also if on a report - then just this might be useful.
http://support.microsoft.com/?kbid=209244
Lots of different takes on it - like
http://www.aspfaq.com/show.asp?id=2506
And one method from right here that might work for you within a query scenario
https://www.experts-exchange.com/questions/21631892/Getting-Median-value.html
But there are many examples of calculating medians out there..
Something like
http://support.microsoft.com/?kbid=210581
Also if on a report - then just this might be useful.
http://support.microsoft.com/?kbid=209244
Lots of different takes on it - like
http://www.aspfaq.com/show.asp?id=2506
And one method from right here that might work for you within a query scenario
https://www.experts-exchange.com/questions/21631892/Getting-Median-value.html
ASKER
"Not sure if you'll be able to get a UDF for an aggregate function of course."
Really? Thats not good because thats what I need. I need it to group by region and then industry and then calculate the median in that.
Is anyone absolutely sure that median can't be done in an aggregate function?
D
Really? Thats not good because thats what I need. I need it to group by region and then industry and then calculate the median in that.
Is anyone absolutely sure that median can't be done in an aggregate function?
D
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This time I should have refreshed. Many things can happen during two phone calls...
I like the SQL solutions in one of Leigh's links, but I'm not quite sure they work in Access as is. Top 1 is not guaranteed to return a single record, which is a problem for subqueries...
(°v°)
I like the SQL solutions in one of Leigh's links, but I'm not quite sure they work in Access as is. Top 1 is not guaranteed to return a single record, which is a problem for subqueries...
(°v°)
One more thing:
There are two types of aggregate functions in Access. SQL aggregate functions like Avg(), First(), etc., which cannot be user-defined, and regular VB "domain aggregate" functions like DAvg(), DLookup(), of which you can create new ones.
Note that other major variants of SQL allow the creation of new aggregate functions, but not JetSQL, as far as I know.
If your case, use DMedian( "AvgOfMEAN", "<your query name>" )
(°v°)
There are two types of aggregate functions in Access. SQL aggregate functions like Avg(), First(), etc., which cannot be user-defined, and regular VB "domain aggregate" functions like DAvg(), DLookup(), of which you can create new ones.
Note that other major variants of SQL allow the creation of new aggregate functions, but not JetSQL, as far as I know.
If your case, use DMedian( "AvgOfMEAN", "<your query name>" )
(°v°)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
u could try not grouping with the fields
did that work?
SELECT Avg(MEAN) AS AvgOfMEAN
FROM (tbl1 INNER JOIN tbl2 ON tbl1.DT = tbl2.DT) INNER JOIN tblRt ON tbl1.ID = tblRt.ID
WHERE (((REGION)="North America") AND ((INDUSTRY)="Transportatio