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

    Unanswered: DINTINCT and SUM Failed?

    I've search DBForum MS Access for DISTINCT SUM but no assistence in my case.

    I'm trying to =Sum(tblSoCalMLS_Download.SalesPrice) for each city.

    ie. LONG BEACH = $22,000,500
    ie. CARSON = $15,345,900

    But I'm getting multiple occurances for all of the cities.

    What am I missing here.

    Code:
    SELECT DISTINCT tblSoCalMLS_Download.CITY, Sum(tblSoCalMLS_Download.SALESPRICE) AS SumOfSALESPRICE
    FROM tblSoCalMLS_Download
    GROUP BY tblSoCalMLS_Download.CLOSEDDATE, tblSoCalMLS_Download.CITY, tblSoCalMLS_Download.COUNTY
    HAVING (((tblSoCalMLS_Download.CLOSEDDATE) Between #1/1/2007# And #1/31/2007#) AND ((tblSoCalMLS_Download.COUNTY)="LA"))
    ORDER BY Sum(tblSoCalMLS_Download.SALESPRICE) DESC;

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No need for distinct (I think). This sort of thing is almost always due to an extra column(s) in the group by clause, a la your group by clause

    Code:
     
    GROUP BY tblSoCalMLS_Download.CLOSEDDATE, tblSoCalMLS_Download.CITY, tblSoCalMLS_Download.COUNTY

    You don't want to group by these columns. However you want to filter them. So you need to move the filter from the Having clause to the....





    Testimonial:
    pootle flump
    ur codings are working excelent.

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

    Whalla . . . that did the trick!

    You know . . . I changed COUNTY and CLOSEDATE to WHERE and it works.

    Thanks much . .

    Here is the corrected code . . .

    Code:
    SELECT tblSoCalMLS_Download.CITY, Sum(tblSoCalMLS_Download.SALESPRICE) AS SumOfSALESPRICE
    FROM tblSoCalMLS_Download
    WHERE (((tblSoCalMLS_Download.CLOSEDDATE) Between #1/1/2007# And #1/31/2007#) AND ((tblSoCalMLS_Download.COUNTY)="LA"))
    GROUP BY tblSoCalMLS_Download.CITY
    ORDER BY Sum(tblSoCalMLS_Download.SALESPRICE) DESC;

Posting Permissions

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