I cannot perform the query that returns the number of municipalities in the reports, example:
|municipio | cantidad |
|----------|----------|
|ACAJETE | 4 |
|PANTEPEC | 5 |
|PUEBLA | 1 |
In the report table: reports table
idReporte
descripcion
localidad_id fk
Locations table:
idLocalidad
nombreLocalidad
municipio_id fk
municipalities table
idMunicipio
nombreMunicipio
region_id fk
Regions table:
idRegion
nombreRegion
To carry out the query that I need, I have related 4 tables which were mentioned above, but I cannot obtain the number of municipalities that were made in the reports. What I have is the following:
SELECT m.nombre, count(m.idMunicipio) as cantidad_municipios
FROM reportes
left join localidades ON reportes.localidad_id = localidades.idLocalidad
left join municipios m on m.idMunicipio = localidades.Municipio_id
left join regiones on regiones.idRegion = m.region_id WHERE regiones.idRegion=4;
It returns this result but it is not what I want and I do not understand how to perform the query
nombre cantidad_municipios
ACAJETE 10
The result I want to obtain for the report is the following:
|municipios | cantidad_municipios |
|-----------|---------------------|
|ACAJETE | 4 |
|PANTEPEC | 5 |
|PUEBLA | 1 |
With these results, I will later make a bar graph that shows an example such as: In acajete, 4 reports were obtained, Pantatepec: 5 reports and so on.
At the end of your query you should add the following:
The reason is when an aggregation function; the
COUNT()
one where you need to group the results of the count made by said function; Now, on the other hand, the column that you will use is:m.nombre
because they are the names of the municipalities which you will show grouped.important detail
I notice that you are making use of a
WHERE
at the end of your query; you should consider that, the order is as followsThat is, he
GROUP BY()
goes after theWHERE