SQL grouping the same data, but showing the photos of the records

Question

I have an SLQ that returns the amount of repeated records grouped together, However my need is to list all photos from duplicate records

SELECT count(Longitude) AS registros, id_local, Longitude,Latitude,nome_local,endereco,pic FROM `locais` GROUP BY Longitude,Latitude ORDER BY `nome_local` ASC

The query returns me that I have 2 records in the same latitude, I need to give me the name of the 3 photos that are in each record, but grouped only with the name of the location

Answer 1

For this situation, I would use Mysql GROUP_CONCAT() in this way, for each different name found during the grouping, I would have the value separated by comma. It would look something like below (imagining that the photos are in the field pic):

SELECT count(Longitude) AS registros, id_local, Longitude,Latitude,nome_local,endereco, GROUP_CONCAT(pic) as pics FROM `locais` GROUP BY Longitude,Latitude ORDER BY `nome_local` ASC

In this way, the code would make the necessary negotiations. The result for this column would be something like "foto1, foto2, foto3...".