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

    Unanswered: Group By yyyy For Each City

    I have years 1998 through 2009 in a data table.

    I want to find the average price for EACH CITY in the table grouped by year. Each city might have as many as 400 entries each.
    Code:
    City	           YEAR	Avg PRICE
    Aliso Viejo	1998	175,238.54
    Aliso Viejo	1999	239,500.00
    Aliso Viejo	2000	182,670.92
    Aliso Viejo	2001	233,000.00
    Aliso Viejo	2002	204,503.82
    Aliso Viejo	2003	308,000.00
    Aliso Viejo	2004	238,018.67
    Aliso Viejo	2005	332,000.00
    Aliso Viejo	2006	279,272.36
    Aliso Viejo	2007	350,000.00
    Aliso Viejo	2008	320,000.00
    Aliso Viejo	2009	332,416.91
    
    Brea	           1998	350,000.00
    Brea	           1999	332,416.91
    Brea	           2000	332,000.00
    Brea	           2001	320,000.00
    Brea	           2002	308,000.00
    Brea	           2003	279,272.36
    Brea	           2004	239,500.00
    Brea	           2005	238,018.67
    Brea	           2006	233,000.00
    Brea	           2007	204,503.82
    Brea	           2008	182,670.92
    Brea	           2009	175,238.54
    My Query . . .

    SELECT DISTINCTROW Format$([TblCARETSData1998_2009CLOSD].[CLOSINGDATE],'yyyy') AS [CLOSEDDATE By Year], TblCARETSData1998_2009CLOSD.City, Avg(TblCARETSData1998_2009CLOSD.ClosePrice) AS [Avg SALESPRICE]
    FROM Tbl_RES_1995_2008, TblCARETSData1998_2009CLOSD
    GROUP BY Format$([TblCARETSData1998_2009CLOSD].[CLOSINGDATE],'yyyy'), TblCARETSData1998_2009CLOSD.City, TblCARETSData1998_2009CLOSD.CommonWalls
    HAVING (((TblCARETSData1998_2009CLOSD.CommonWalls) Like "*attached*"));

    I will then run the query for . . .
    HAVING (((TblCARETSData1998_2009CLOSD.CommonWalls) Like "*Detached*"));

    Here is what I getting:

    Aliso Viejo 1998 175,238.54
    Aliso Viejo 1998 239,500.00
    Aliso Viejo 1999 182,670.92
    Aliso Viejo 1999 233,000.00
    Aliso Viejo 2000 204,503.82
    Aliso Viejo 2000 308,000.00
    Aliso Viejo 2001 238,018.67
    Aliso Viejo 2001 332,000.00
    Aliso Viejo 2002 279,272.36
    Aliso Viejo 2002 350,000.00
    Aliso Viejo 2003 320,000.00
    Aliso Viejo 2003 332,416.91
    Aliso Viejo 2003 302,500.00
    Aliso Viejo 2004 436,532.62
    Aliso Viejo 2004 460,000.00

    Should only be one an average for years 1998 through 2009.

    Any assistance would be WONDERFUL!

    Thanks . . . Rick

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SELECT DISTINCTROW Format$([TblCARETSData1998_2009CLOSD].[CLOSINGDATE],'yyyy') AS [CLOSEDDATE By Year], TblCARETSData1998_2009CLOSD.City, Avg(TblCARETSData1998_2009CLOSD.ClosePrice) AS [Avg SALESPRICE]
    FROM Tbl_RES_1995_2008, TblCARETSData1998_2009CLOSD
    GROUP BY Format$([TblCARETSData1998_2009CLOSD].[CLOSINGDATE],'yyyy'), TblCARETSData1998_2009CLOSD.City, TblCARETSData1998_2009CLOSD.CommonWalls
    HAVING (((TblCARETSData1998_2009CLOSD.CommonWalls) Like "*attached*"));
    you have a (possibly unintentional) cross join between those two tables -- how are they supposed to be related?

    as for your apparent duplication of results, try taking the common walls out of the GROUP BY clause

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

Posting Permissions

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