# Thread: Aggregate Average Query Problem

1. Registered User
Join Date
Mar 2004
Location
California
Posts
502

## 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.

thanks much

rick

2. Registered User
Join Date
May 2009
Posts
509
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```

3. L33t Helpa Munky
Join Date
Nov 2007
Location
Posts
4,049
Why not use the Design view in a query then?! I find it a lot easier that writing straight SQL.

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by StarTrekker
I find it a lot easier that writing straight SQL.

5. Registered User
Join Date
Mar 2004
Location
California
Posts
502
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?

Love this forum.

Thanks for all of your help and suggestions.

Rick

6. L33t Helpa Munky
Join Date
Nov 2007
Location
Posts
4,049
Originally Posted by r937

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 ^^

7. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by Rick Schreiber
Rudy . . . If I purchased your book would I be able to understand your superior advanced technique?
if the advanced technique you refer to is to write WHERE clauses instead of the stupid HAVING clauses that Access insists on giving you in Design View, the answer is yes

8. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
TBF, you can change these to WHERE in the Query Builder without touching the SQL.

BTW - Stealth_DBA: excellent post!

9. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
that's not the only example of "limited" SQL, pootsie -- try joining to a derived table, and you'll end up writing a saved query instead

those of you who might also be web developers will understand when i draw the parallel between html that you write yourself and html that is produced by a tool such as, oh, frontpage for example

and just for my own edification, poots, just how do you change a recalcitrant HAVING clause to WHERE in the Query Builder? is the answer to open it in SQL view and change it there?

10. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Originally Posted by r937
that's not the only example of "limited" SQL, pootsie -- try joining to a derived table, and you'll end up writing a saved query instead
OMG - you mean you can't do that? That was one of the flipping points I made to you when I was outlining my problems with the Query Builder!

Originally Posted by r937
and just for my own edification, poots, just how do you change a recalcitrant HAVING clause to WHERE in the Query Builder?
In the query builder, change the "Total:" drop down value from "Group By" to "Where". This removes the column from the group by and select clauses, and moves the criteria to the where clause.

Originally Posted by r937
is the answer to open it in SQL view and change it there?
No

11. Registered User
Join Date
Mar 2004
Location
California
Posts
502

## Sheesh . . .

and you wonder why I get confused!

WHERE . . . HAVING . . . SET . . .GROUP BY . . . ORDER BY . . . DESC 15 etc . . .

Does anybody speak English any more?

12. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by Rick Schreiber
Does anybody speak English any more?
been tried; didn't work

sql is cleaner

plus, a ~lot~ easier to get really good at

13. L33t Helpa Munky
Join Date
Nov 2007
Location