Sql, sum of several

Question

Hello, I have a bank with 3 important tables, state,city and orders need to rank the states than have more requests to q have less but the orders are related only to city table. so I need to make a Count for each city and add the Counts of the cities that belong to the same state. but I have no idea how to do that. someone can help me?

state table structure:

structure of the city table:

order table structure:

Answer 1

Considering generic names of tables and fields:

SELECT es.nome as estado, ci.nome as cidade, COUNT(pe.id)
FROM pedidos pe
LEFT JOIN cidade ci ON ci.id = pe.cidade
LEFT JOIN estado es ON es.id = ci.estado
WHERE pe.datapedido BETWEEN '01/01/2018' AND '31/01/2018'
GROUP BY es.nome, ci.nome

---

In its structure:

SELECT es.estado, COUNT(pe.id)
FROM pedidos pe
LEFT JOIN cidades ci ON ci.id = pe.cidade_id
LEFT JOIN estado es ON es.id = ci.estado_id
GROUP BY es.estado