0

I am working with a French Real Estate database in which all the sales in France are recorded per semester. It has 3 Tables:

  1. Localite, which has city (Commune) and state (Departement) data,
  2. Bien, which has house data (m², address, etc.) and is only used in this query because the way the tables are linked, and
  3. Mutation, which has price (ValeurFonciere) data.

Which were created using:

CREATE TABLE Localite (
Commune_ID SMALLINT UNSIGNED NOT NULL,
Commune VARCHAR(50) NOT NULL,
Departement VARCHAR(3) NOT NULL,
PRIMARY KEY (Commune_ID)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Bien (
Bien_ID SMALLINT UNSIGNED NOT NULL,
SurCar1 FLOAT NOT NULL,
TypeLoc VARCHAR(15) NOT NULL,
NoPP SMALLINT NOT NULL,
NoVoie VARCHAR(5) NOT NULL,
TypeVoie VARCHAR(10) NOT NULL,
NomVoie VARCHAR(50) NOT NULL,
CodePostal VARCHAR(5) NOT NULL,
Commune_ID SMALLINT UNSIGNED NOT NULL,
PRIMARY KEY (Bien_ID)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Mutation (
Mutation_ID SMALLINT UNSIGNED NOT NULL,
DateMutation DATETIME NOT NULL,
ValeurFonciere INT UNSIGNED NOT NULL,
Bien_ID SMALLINT UNSIGNED NOT NULL,
PRIMARY KEY (Mutation_ID)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Example Mutation Data Example Localite Data

I need to find the 3 cities with the highest average sales price, but for only a few states.

When I use this:

SELECT Departement, Commune, avg(ValeurFonciere) as Prix_Moy
FROM localite, bien, mutation
where mutation.Bien_ID = bien.Bien_ID
And bien.Bien_ID = localite.Commune_ID
AND Departement in (6, 13, 33, 59, 69) 
GROUP BY Commune
ORDER BY Commune DESC
limit 3;

I get the Top 3 across all 5 Departements (i.e. 6, 13, 33, 59, 69). In France each Departement (State) has its own identifying number, so those numbers are the Departements I am interested in.

But how do I get the top 3 for each of those Departements (so, 15 Commune in total)?

I have looked on here and have found several solutions using a rank() function with a partition, but I don't know if that's the correct solution here because I can't tell it to look at only the 5 Departements I need.

Any help would be greatly appreciated.

casper
  • 1
  • 3
  • 1
    Welcome to Stack Overflow. Please see https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question/271056#271056 for SQL related questions. – Progman Jul 21 '21 at 13:41
  • @Progman Doesn't tell me how to resolve my issue as far as I can tell – casper Jul 21 '21 at 14:17
  • It's not an answer for your question. This link informs you how you should expand your post with additional information and data related to your SQL problem. Add the table structure as `CREATE TABLE` statements, example data in these tables and the result you want from them. Keep in mind that we currently do not see the content of your tables or the table structure (with the column types) and the foreign keys you might have. We have no idea what these ids "6, 13, 33, 59, 69" mean. – Progman Jul 21 '21 at 14:28
  • Ah, ok. My bad. I forgot that people don't necessarily know that each French Departement (State) has its own number, and the same for each Commune (City). For the US this would Alabama = 1, Alaska = 2, etc. I will edit my post accordingly. – casper Jul 21 '21 at 14:53
  • ```WITH VF_par_Commune AS (SELECT Departement, Commune, avg(ValeurFonciere) as AVF FROM localite, bien, mutation where mutation.Bien_ID = bien.Bien_ID And bien.Commune_ID = localite.Commune_ID AND Departement IN (6,13,33,59,69) GROUP BY Departement, Commune) SELECT Departement, Commune, round(AVF,2) AS Prix_Moy FROM ( SELECT Departement, Commune, AVF, rank() OVER (PARTITION BY Departement ORDER BY AVF DESC) AS rang FROM VF_par_Commune) AS result WHERE rang <= 3;``` – casper Jul 21 '21 at 19:26

0 Answers0