[help] query!

michellemabelle · #1 (**permalink**) 04-11-11, 02:42

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?!

shammat · #2 (**permalink**) 04-11-11, 03:02

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

michellemabelle · #3 (**permalink**) 04-11-11, 03:09

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!

shammat · #4 (**permalink**) 04-11-11, 03:37

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

michellemabelle · #5 (**permalink**) 04-11-11, 03:59

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

shammat · #6 (**permalink**) 04-11-11, 04:10

Post the table definition as CREATE TABLE statements
Post sample data as INSERT INTO statements

michellemabelle · #7 (**permalink**) 04-11-11, 04:13

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

michellemabelle · #8 (**permalink**) 04-11-11, 05:22

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

Internet Services

Web Development

Digital Marketing

Consumer Tech