Top x of A used in Avg of B

[kohai]

Hi,

I have a table that has 3 fields, dDate, Return, MCap

I am trying to get a query that will give me the average of Return for the
Top 10 companies with the largest MCap for each day.

I have the below working, but can only do so for an individual day. I can't
get it to work for all the days.

SELECT [DailyData].dDate, Avg([DailyData].Return) AS AvgOfReturn
FROM DailyData
WHERE ((([shares]*[price]) In (SELECT TOP 10 ([shares]*[price]) AS mcap FROM
DailyData WHERE ((([DailyData].dDate)=#11/15/2005#)) ORDER BY
([shares]*[price]) DESC) AND (([DailyData].dDate)=#11/15/2005#))
GROUP BY [DailyData].dDate;

If I expand the date range, it takes the the top 10 companies over all of
the dates and returns the avg. I want it for each day, and the top 10 can
and will change each day.

Thank you.
kohai

 

[G. Vaught]

You might have to throw in a Group by first to group the data by date, then
use the Select Top Ten. Not sure you can use in current SQL Statement or if
you have to use an independent query to accomplish your task.

 

[John Spencer]

Try referencing the dDate of the outer query in the subquery.

SELECT [DailyData].dDate,
Avg([DailyData].Return) AS AvgOfReturn
FROM DailyData
WHERE [shares]*[price] In
(SELECT TOP 10 ([shares]*[price]) AS mcap FROM
DailyData as Tmp
WHERE Tmp.dDate=DailyData.dDate
ORDER BY ([shares]*[price]) DESC)
AND [DailyData].dDate=#11/15/2005#
GROUP BY [DailyData].dDate;

Now all you need to do is set the dDate range in the Outer query.

 

[kohai]

Thank you both for the responses. John, that change did the trick!!

Thanks a TON!!!

kohai

Try referencing the dDate of the outer query in the subquery.

SELECT [DailyData].dDate,
Avg([DailyData].Return) AS AvgOfReturn
FROM DailyData
WHERE [shares]*[price] In
(SELECT TOP 10 ([shares]*[price]) AS mcap FROM
DailyData as Tmp
WHERE Tmp.dDate=DailyData.dDate
ORDER BY ([shares]*[price]) DESC)
AND [DailyData].dDate=#11/15/2005#
GROUP BY [DailyData].dDate;

Now all you need to do is set the dDate range in the Outer query.

Hi,

I have a table that has 3 fields, dDate, Return, MCap

I am trying to get a query that will give me the average of Return for the
Top 10 companies with the largest MCap for each day.

I have the below working, but can only do so for an individual day. I
can't
get it to work for all the days.

SELECT [DailyData].dDate, Avg([DailyData].Return) AS AvgOfReturn
FROM DailyData
WHERE ((([shares]*[price]) In (SELECT TOP 10 ([shares]*[price]) AS mcap
FROM
DailyData WHERE ((([DailyData].dDate)=#11/15/2005#)) ORDER BY
([shares]*[price]) DESC) AND (([DailyData].dDate)=#11/15/2005#))
GROUP BY [DailyData].dDate;

If I expand the date range, it takes the the top 10 companies over all of
the dates and returns the avg. I want it for each day, and the top 10 can
and will change each day.

Thank you.
kohai