Sql, sum of several counts

Question

Sql, sum of several counts

Navigation

#1 by (4 votes)

-1

Hello, I have a bank with 3 important tables, state, city and orders I need to rank the states of the one that has the most requests to have less, but the orders are only related to the 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 it. Can someone help me?

state table structure:

citytablestructure:

ordertablestructure:

mysql sql mysqli mysql-workbench

asked by anonymous 10.09.2018 / 15:41

1 answer

How to detect if the string entered is longer than 6 characters? [closed] Calculate SQL mean with hour field

score 4 · Accepted Answer

Consider 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