If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Aggregate Average Query Problem

This query list each city multiple times, I believe, based on the number of occurances in the date range.

If there are 20 different dates each with a salePrice within the date range the city will show all 20 occourances.

What I need is for the query to average ALL salePrice as a whole for the DATE RANGE. GROUP BY CITY SORTED BY CITY

Code:

SELECT TblCARETSData.City, Avg(TblCARETSData.ClosePrice) AS [Avg Of ClosePrice], Count(TblCARETSData.City) AS CountOfCity
FROM TblCARETSData
GROUP BY TblCARETSData.City, TblCARETSData.ClosingDate, TblCARETSData.County
HAVING (((TblCARETSData.ClosingDate) Between #1/1/2009# And #1/31/2009#) AND ((TblCARETSData.County)="orange"));

I just don't understand how to write aggregate queries. My records are alsmost always in one table.

Rick, first, your originial query is doing a lot more work than it needs to.

There are 2 points where filtering of rows is done. One is the Where clause and the other is the Having clause. There is a big difference where they are applied.

The Where clause filters rows coming from the table to the initial result set.
After this the Group by is applied to this result set.
After that the Having clause is applied the the rows that have been grouped.

Applying this to your query, and you will see that you are returning every row in your table (there is no Where clause). All of this data is sorted and Grouped. Finally after all that data has been moved around and manipulated, the Having clause filters out rows by Date and County.

Move and change the Having clause to a Where clause what will happen is only rows in the date range for one county would be initially returned. This much smaller set of data would be sorted and Grouped.

Something like this would be more efficient:

Code:

SELECT City
, Avg(ClosePrice) AS AvgOfClosePrice
, Count(City ) AS CountOfCity
FROM TblCARETSData
WHERE ClosingDate Between #1/1/2009# And #1/31/2009#
AND County="orange";
GROUP BY City
, ClosingDate

As for the query you want, it is very similar, just remove the ClosingDate from the Group By:

Code:

SELECT City
, Avg(ClosePrice) AS AvgOfClosePrice
, Count(City ) AS CountOfCity
FROM TblCARETSData
WHERE ClosingDate Between #1/1/2009# And #1/31/2009#
AND County="orange";
GROUP BY City
ORDER BY City

Hey Stelth DBA . . . you've explained it in a rudimentary way that I was able to grasp.

I copied your words and of course the text also.

Thanks very much.

I see Rudy has chimed in too.

Star Trekker mentioned doing this in datasheet design view. That's where I started, using the query wizard using very basic fields believing less is more. I was not able to obtain the the final results as suggested by Stealth DBA. Maybe that was my fault.

Rudy . . . If I purchased your book would I be able to understand your superior advanced technique?

I don't find it limiting at all... if I need to do something beyond what the designer can do, then I modify the SQL. I just find it a lot easier to drag and drop 15 fields into the grid rather than have to type all their names, separated by commas Lazy I guess ^^

TBF, you can change these to WHERE in the Query Builder without touching the SQL.
You do like playing devil's advocate don't you Rudy? You and me have argued about this where you were trumpeting Access!