Top x of A used in Avg of B

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kohai - 16 Nov 2005 23:54 GMT

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

Reply to this Message

G. Vaught - 17 Nov 2005 06:01 GMT

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.

> Hi,
>
[quoted text clipped - 21 lines]
> Thank you.
> kohai

Reply to this Message

John Spencer - 17 Nov 2005 12:49 GMT

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,
>
[quoted text clipped - 21 lines]
> Thank you.
> kohai

Reply to this Message

kohai - 17 Nov 2005 15:16 GMT

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.
>
[quoted text clipped - 36 lines]
> > Thank you.
> > kohai

Reply to this Message