Results 1 to 8 of 8

Thread: [help] query!

  1. #1
    Join Date
    Apr 2011
    Posts
    5

    Unanswered: [help] query!

    Hi to everyone!
    I'd need some help with this query:

    I have to find the city with highest average price/mq of buildings with private use among cities of the same region.

    This is what I've written till now:

    Code:
    SELECT address.region, address.city, AVG(announce.price/building.mq) 
    FROM announce JOIN building ON (announce.id_i = building.id_i)
    JOIN address ON (building.id_i = address.id_i)
    WHERE bulding.use = 'private' AND price IS NOT NULL
    GROUP BY address.region, address.city
    ORDER BY address.region, address.city
    With this code I find the average price/mq for every city of every region.
    Since I can't use something like MAX(AVG(announce.price/building.mq)) in Postgres,
    how can I find a city for every region?!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Code:
    SELECT region, 
           city, 
           max(avg_price) 
    FROM (
        SELECT address.region, address.city, AVG(announce.price/building.mq) as avg_price
        FROM announce JOIN building ON (announce.id_i = building.id_i)
        JOIN address ON (building.id_i = address.id_i)
        WHERE bulding.use = 'private' AND price IS NOT NULL
        GROUP BY address.region, address.city
    ) t
    GROUP BY region, city
    ORDER BY region, city

  3. #3
    Join Date
    Apr 2011
    Posts
    5
    I tried but the result is the same of my previous query!
    I need the city with the highest cost among his region, but I got all the cities of all the regions!

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Code:
    SELECT adr1.region, 
           adr1.city, 
           AVG(ann1.price/b1.mq) as avg_price
    FROM annnonce ann1 
       JOIN building b1 ON (ann1.id_i = b1.id_i)
       JOIN address adr1 ON (b1.id_i = adr1.id_i)
    WHERE b1.use = 'private' 
      AND ann1.price IS NOT NULL
    GROUP BY address.region, address.city
    HAVING AVG(ann1.price/b1.mq) = (SELECT max(ann2.price/b2.mq)
                                    FROM announce ann2 
                                       JOIN building b2 ON (ann2.id_i = b2.id_i)
                                       JOIN address adr2 ON (b2.id_i = adr2.id_i)
                                    WHERE adr2.region = adr1.region
                                      AND ann2.price IS NOT NULL)
    GROUP BY adr1.region, adr1.city

  5. #5
    Join Date
    Apr 2011
    Posts
    5
    Tried.
    But I got only a result (and neither the max result!).

    Theorically my result should be 2, 'cos I have 4 cities in 2 different regions:

    Lombardia - Milano
    Lombardia - Busto
    Toscana - Bolgheri
    Toscana - Livorno

    And my results should be:

    Lombardia - Milano
    Toscana - Livorno

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Post the table definition as CREATE TABLE statements
    Post sample data as INSERT INTO statements

  7. #7
    Join Date
    Apr 2011
    Posts
    5
    The problem is that my tables are in Italian (I've translated everything here)
    and i don't have the insert into 'cos I've inserted the data via website

  8. #8
    Join Date
    Apr 2011
    Posts
    5
    SOLVED!

    TRANSLATION:
    regione = region
    comune = city
    annuncio = announce
    prezzo = price
    immobile = building
    recapito = address
    uso = use
    privato = private

    SELECT r1.regione, r1.comune, AVG(annuncio.prezzo/immobile.mq) AS costo_medio
    FROM annuncio JOIN immobile ON (annuncio.id_i = immobile.id_i)
    JOIN recapito r1 ON (immobile.id_i = r1.id_i)
    WHERE immobile.uso = 'privato' AND prezzo IS NOT NULL
    GROUP BY r1.regione, r1.comune
    HAVING AVG(annuncio.prezzo/immobile.mq) > ALL
    (SELECT AVG(annuncio.prezzo/immobile.mq)
    FROM annuncio JOIN immobile ON (annuncio.id_i = immobile.id_i)
    JOIN recapito r2 ON (immobile.id_i = r2.id_i)
    WHERE r1.regione = r2.regione
    )
    ORDER BY r1.regione, r1.comune

Posting Permissions

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