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.