SQL queries of state names with their number of cities

Asked

Viewed 76 times

-1

Good evening, I am starting to study SQL and I am making small queries in the database to practice. But I fall for the one that asks to display the names of the states with the respective number of cities and I just can’t. I can relate cities to their states but I can’t just leave the state with the respective number of cities.

mysql sql database

  • The site has busque group by

1 answer

1

Good evening, Vinicius, sir. Ideally you would put the table scheme in question, but I believe it is a 'city' table with a 'state' field inside it.

Try this:
SELECT estado, COUNT(nome_cidade) AS 'Quantidade de Cidades' <br> FROM cidade <br> GROUP BY estado

"The GROUP BY clause groups lines based on similarities between them". In this case it is grouping the number of cities that share the same state as.

  • I forgot to say the tables but I was able to solve with: select and.state name, Count(*) quantity_city from state and Inner Join municipality m on e.codestate = m.codestate group by e.name order by quantity_city

Browser other questions tagged mysql sql database

You are not signed in. Login or sign up in order to post.