Top N Query based on group averages

[jderrig]

I need to create a query where in the end, I will have four rows of data based on based on two combinations of WaterSourceType and Crop.

I need the query to bring back the results of the average Top N (lets say Top 10%) for each combination.

I have tried this every which way and I can't seem to get it grouped like I want it. I NEED to have four distinct rows with the average of the ProfitPerBushel for each grouping.

Basically, what this does is show me the average profitablity of the top 10% in each grouping.

WaterSourceType | Crop | ProfitPerBushel

Irrigated | Soybeans | ProfitPerBushel
Non-Irrigated | Soybeans | ProfitPerBushel
Irrigated | Corn | ProfitPerBushel
Non-Irrigated | Corn | ProfitPerBushel

Any ideas would be great. Thanks

 

[BlueIshDan]

I need a better understanding of your database before I can attempt to answer this.

 

[jderrig]

I have three tables:

tblCooperative -> CooperativeCity, CooperativeState, CooperativeName, CooperativeNumber
tblGrower -> CustomerName, CustomerNumber, CustomerState, CooperativeNumber
tblGrowerData -> WaterSourceType, Crop, CustomerNumber, CustomerYear, NumberofACres, FarmExpenses (there are more expenses but it's a lot so I will shorten it down to one)

Each customer can up to four types entries in the GrowerData table based on WaterSourceType (Irrigated or Non-Irrigated) and Crop (Corn or soybeans). Our calculations are profit per acre for each of the four entries a customer can have (Irrigated Corn, Non-Irrigated Corn, Irrigated Soybeans, Non-Irrigated Soybeans).

I need to calculate the top 10% most profitable customers (averaged together) in each of the 4 groupings.

I hope this is enough. I would post the db but would have to clear out confidential info already entered.

I can do this seperately of course, but would like to have it all in one query (four rows) to make it easier for the end user to operate.

 

[jdraw]

Since it is the query you're having trouble with, could you provide us a copy of your db with a few sample records. It can be a minimal database with just enough info to represent your intent. Then readers can focus on the data and query.
For example:
Create a new database with the 3 tables you mention. Then populate the tables with data from a few growers etc.

EDIT:

I just saw your database on UA. How do you determine ProfitPerBushel?
A quick query

SELECT tblGrowerData.WaterSourceType
, tblGrowerData.Crop
, Avg(tblGrowerData.Yield) AS AvgOfYield
FROM tblGrowerData
GROUP BY tblGrowerData.WaterSourceType, tblGrowerData.Crop
ORDER BY tblGrowerData.Crop, tblGrowerData.WaterSourceType;

Gives these results

WaterSourceType Crop AvgOfYield
Irrigated corn 76666.6666666667
Non-Irrigated corn 100000
Irrigated soybeans 73666.6666666667
Non-Irrigated soybeans 67333.3333333333

but this is yield not ProfitPerBushel and doesn't deal with Top N

 

[jderrig]

Here is the db attached.

 

[jdraw]

Please see the edit in my last post.

 

[jderrig]

I am using FarmCostAcres/AcresPlanted to get the total profitability.


EDIT:

I just saw your database on UA. How do you determine ProfitPerBushel?
A quick query

SELECT tblGrowerData.WaterSourceType
, tblGrowerData.Crop
, Avg(tblGrowerData.Yield) AS AvgOfYield
FROM tblGrowerData
GROUP BY tblGrowerData.WaterSourceType, tblGrowerData.Crop
ORDER BY tblGrowerData.Crop, tblGrowerData.WaterSourceType;

Gives these results

WaterSourceType Crop AvgOfYield
Irrigated corn 76666.6666666667
Non-Irrigated corn 100000
Irrigated soybeans 73666.6666666667
Non-Irrigated soybeans 67333.3333333333

but this is yield not ProfitPerBushel and doesn't deal with Top N[/QUOTE]

 

[jderrig]

This only works to get the average of each, which I have. I cann't get it to bring back the top 10% averaged in each group.

I am using FarmCostAcres/AcresPlanted to get the total profitability.


EDIT:

I just saw your database on UA. How do you determine ProfitPerBushel?
A quick query

SELECT tblGrowerData.WaterSourceType
, tblGrowerData.Crop
, Avg(tblGrowerData.Yield) AS AvgOfYield
FROM tblGrowerData
GROUP BY tblGrowerData.WaterSourceType, tblGrowerData.Crop
ORDER BY tblGrowerData.Crop, tblGrowerData.WaterSourceType;

Gives these results

WaterSourceType Crop AvgOfYield
Irrigated corn 76666.6666666667
Non-Irrigated corn 100000
Irrigated soybeans 73666.6666666667
Non-Irrigated soybeans 67333.3333333333

but this is yield not ProfitPerBushel and doesn't deal with Top N

[/QUOTE]

 

[jdraw]

And where do you find these fields??
I am using FarmCostAcres/AcresPlanted to get the total profitability.

 

[jderrig]

Sorry, I have them calculated in another query that I pull from.