Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Displays Multiples of Same City

    This query displays multiples of same city rather than average salesprice for each individual city.

    What am I mising here?

    Thanks . . . Rick


    Code:
    SELECT TblSoCalMLS_Download.CITY, Avg(TblSoCalMLS_Download.SALESPRICE) AS AvgOfSALESPRICE
    FROM TblSoCalMLS_Download
    GROUP BY TblSoCalMLS_Download.CITY, TblSoCalMLS_Download.CLOSEDDATE
    HAVING (((TblSoCalMLS_Download.CLOSEDDATE)>=#1/1/2009# And (TblSoCalMLS_Download.CLOSEDDATE)<=#1/31/2009#))
    ORDER BY TblSoCalMLS_Download.CITY;

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    I might be wrong but I don't understand why you use HAVING to filter your records. According to what I know:
    The presence of the clause GROUP BY is necessary as soon as the clause of selection, or the filter WHERE, or the joints contain simultaneously calculations of aggregation and columns outside the calculations of aggregation. Furthermore, all the columns represented outside the calculations of aggregation have to appear in the clause GROUP BY.
    See also: SQL HAVING Clause

    Have a nice day!

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Hi Sinndho - thanks you for your reply - and the link.

    After looking at the link and what you pointed out this is what I came up with and it works perfectly.

    Code:
    SELECT TblSoCalMLS_Download.CITY, Avg(TblSoCalMLS_Download.SALESPRICE) AS [AVERAGE SALESPRICE], Count(TblSoCalMLS_Download.CITY) AS CountOfCITY
    FROM TblSoCalMLS_Download
    WHERE (((TblSoCalMLS_Download.CLOSEDDATE)>=#2/1/2009# And (TblSoCalMLS_Download.CLOSEDDATE)<=#2/28/2009#))
    GROUP BY TblSoCalMLS_Download.CITY
    ORDER BY TblSoCalMLS_Download.CITY;
    Rick

Posting Permissions

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