Group By returns 2x the grouped category

[Sako]

Hi,

I need to make an AVG() per category. The average price will select the top 200 most recent days (the format of the date is a txt format like "20090819" ) of each category. What I also need is that if the category does not have 200 days, then not to select the category (If there is a category of only 15 days, I don't need to have the average, but just to ignore that category)

Currently, I am capable of doing that, but only by using three queries one on top of each other... and it does take some time to run it too.

1. I would select the top 200 days of every category. This will return the top 200, even if there are many categories with less than 200 days.

2. I would do a count() and group by category to see what categories contain 200 (Using the 1st query above)

3. I would run the AVG() price on the 2nd Query.

I know there is probably a way to combine this, since it doesn't look that hard...
But I can't seem to do it in one single query...

The closest attempt I've got from an Access user is this code:
************************************************
SELECT YourTable.Cat, YourTable.DateField, Avg(YourTable.Price) AS AvgOfPrice
FROM YourTable
GROUP BY YourTable.Cat, YourTable.DateField
HAVING (((YourTable.Cat) In (SELECT YourTable.Cat FROM YourTable
GROUP BY YourTable.Cat HAVING (((Count(YourTable.Cat))>200))))
AND ((YourTable.DateField) In (SELECT top 200 DateField from YourTable )));
**************************************************************

It looks like it's working fine, but the only problem is that it returns 2 instances of every Category (named Cat here) and each one would get the 1st and 2nd DateField.

I know that Group by is supposed to return only ONE category, not two...

 

[jjturner]

Not quite sure why you're getting *doubled* records, unless there's an issue with lacking aliases. From the looks of it, you should be getting a "running average" since every date which meets the criteria will be included in the results - meaning you should be seeing multiple dates per category.

For an alternative "running average" which doesn't require subqueries, you may want to try:

SELECT t1.Cat, t1.DateField, Avg(t2.Price) AS AvgOfPrice
FROM YourTable t1 INNER JOIN YourTable t2 ON
   t1.Cat = t2.Cat AND t1.DateField >= t2.DateField
WHERE t2.DateField >= t1.DateField - 200
GROUP BY t1.Cat, t1.DateField
HAVING Count(t2.Cat) >= 200;

This is assuming each category would only have 1 Price per date. If a category can have multiple Price records per date, then I believe you would need to use a subquery.

HTH,
John