How to perform a query on mysql using count?

Question

How to perform a query on mysql using count?

Navigation

#1 by (1 votes)

2

I can not perform the query that returns me the number of municipalities of the reports, example:

|municipio | cantidad |
|----------|----------|
|ACAJETE   |   4      |
|PANTEPEC  |   5      |
|PUEBLA    |   1      |

In the report table: table reports

idReporte
descripcion
localidad_id   fk

Table locations:

idLocalidad
nombreLocalidad
municipio_id   fk

Table municipalities

idMunicipio
nombreMunicipio
region_id    fk

Regions table:

idRegion
nombreRegion

To carry out the consultation I need I have related 4 tables which were mentioned above, but I can not get the amount 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 to me but it is not what I want and I do not understand how to make the query

nombre           cantidad_municipios
ACAJETE              10

The result I want to obtain for the report is as follows:

|municipios | cantidad_municipios |
|-----------|---------------------|
|ACAJETE    |   4                 |
|PANTEPEC   |   5                 |
|PUEBLA     |   1                 |

With these results I will make a bar graph that shows an example like: In acajete 4 reports were obtained, pantatepec: 5 reports and so on

mysql sql

asked by Luis Hernandez 07.11.2018 в 00:56

source

1 answer

Doubt ascending / descending order Does not show the value of an XMl node

score 1 · Accepted Answer

At the end of your query you should add the following:

GROUP BY (m.nombre)

The reason you are dealing with an aggregation function; that of COUNT() you need to group the results of the count that does this function; now on the other hand the column 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 follows

WHERE columnName .......
GROUP BY(m.nombre);

That is, the GROUP BY() goes after the WHERE