Results 1 to 13 of 13
  1. #1
    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. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Why not use the Design view in a query then?! I find it a lot easier that writing straight SQL.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by StarTrekker
    I find it a lot easier that writing straight SQL.
    how sadly limiting

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    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. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by r937
    how sadly limiting

    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 ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote 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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    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.
    You do like playing devil's advocate don't you Rudy? You and me have argued about this where you were trumpeting Access!

    BTW - Stealth_DBA: excellent post!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote 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!

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

    Quote Originally Posted by r937
    is the answer to open it in SQL view and change it there?
    No
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Smile 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. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    English is probably the most confusing language in the world. I would agree that SQL would be easier to get really good at than English!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •