Home / Questions / 563383
Accepted
developer
Asked: 2022-10-21 10:11:10 +0800 CST 2022-10-21 10:11:10 +0800 CST

Query with multiple columns and a count

  • 772

I need to get a query of how many events there were in a city by city. Currently I have this query that gets me the name of the city and the number of events that have occurred in a city:

SELECT ci.name as name, COUNT(*) as num
FROM ciudad ci, evento e, local l
WHERE e.idlocal = l.id
AND l.idciudad = ci.id 
GROUP BY NAME;

This returns me:

Yam NUMBER
Madrid 35
Barcelona 24
Valencia 5
Seville 3

The problem is that I want to get the identifier of the city so that later when they click on the html they go to a specific city identifier.

The expected result would be the following:

ID YAM NUMBER
two Madrid 35
3 Barcelona 24
4 Valencia 5
5 Seville 3

How could I get this out? It doesn't let me add one more field in the select because it gives me an error in the query for the COUNT.

mysql

1 Answers

  1. Best Answer

    without structures and some example it is more difficult to understand the inconvenience but a priori the only thing you should change is the aggregation level.

    SELECT ci.id, ci.name as name, COUNT(*) as num
FROM ciudad ci, evento e, local l
WHERE e.idlocal = l.id
   AND l.idciudad = ci.id 
GROUP BY ci.id, ci.name;
    • 2