How to get top 3 averages with a WHERE clause over several groups and Tables?

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 selected (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.



Read more here: https://stackoverflow.com/questions/68472106/how-to-get-top-3-averages-with-a-where-clause-over-several-groups-and-tables

Content Attribution

This content was originally published by Casper at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: