Consulta com Group By e Count em outra tabela

[danicarla 3]

Olá pessoal, tudo bom? Tenho uma tabela de região e outra de times...
Preciso fazer uma consulta pra exibir quantos times eu tenho por região, minhas tabelas estão assim:

tabela regioes

id  regiao    cidade
1   Amarela   São Paulo
2   Amarela   Guarulhos
3   Amarela   Osasco
4   Amarela   Santos
5   Verde     São Paulo
6   Verde     Santos
7   Azul      Santos
8   Azul      Jundiai
9   Azul      Guarulhos

Tabela times

id  id_regiao  time
1   3          America
2   5          Bufalos       
3   2          Futebol 7
4   8          Bar sem lona
5   7          Inter de Limão
6   1          Atletico Maneiro
7   3          Baile de Monique
8   2          Barra Leverkusen
9   2          Piritubense

A minha consulta até retorna as regiões, mas aparece o numero de times como zero, minha consulta está assim:

SELECT DISTINCT regiao,
       (SELECT COUNT(id) FROM times
        WHERE times.id_regiao = regioes.id) AS Total
FROM regioes
GROUP BY regiao

Será que podem me ajudar?

[wootzor 103]

Seria isto?:

 

SELECT regiao, COUNT(`time`) AS qtd_equipas
FROM regioes JOIN times
ON regioes.id = times.id_regiao
GROUP BY regiao;

 

[danicarla 3]

A sua consulta deu certo em partes rsrsrs
Ele não lista a região que não tem nenhum time.... e eu precisaria que aparecesse com zero times...
Eu alterei o JOIN para LEFT JOIN e funcionou mas se eu colocar algum WHERE na tabela dos times ele também não exibe as regiões que não possuem nenhum time 
 

SELECT regiao, COUNT(`time`) AS qtd_equipas
FROM regioes LEFT JOIN times
ON regioes.id = times.id_regiao
WHERE times.numero_jogadores>11
GROUP BY regiao

 

[danicarla 3]

Eu consegui fazer assim, mas a consulta ficou muito pesada e demorada

SELECT p.regiao, 
   (SELECT COUNT(a.id) FROM times AS a 
   INNER JOIN regiao AS e ON e.id=a.id_regiao
   WHERE a.numero_jogadores>11 AND e.regiao=p.regiao) AS quant
FROM regioes AS P
WHERE p.cidade='São Paulo' GROUP BY p.região

 

Fica mais rápido eu fazer uma consulta agrupando por bairro e dentro do while do php fazer outra consulta pra contar a quantidades de times do que usar essa consulta que fiz ai em cima
 

[kim.y 11]

 

Em 10/01/2018 at 15:14, danicarla disse:

A sua consulta deu certo em partes rsrsrs
Ele não lista a região que não tem nenhum time.... e eu precisaria que aparecesse com zero times...
Eu alterei o JOIN para LEFT JOIN e funcionou mas se eu colocar algum WHERE na tabela dos times ele também não exibe as regiões que não possuem nenhum time 
 

SELECT regiao, COUNT(`time`) AS qtd_equipas
FROM regioes LEFT JOIN times
ON regioes.id = times.id_regiao
WHERE times.numero_jogadores>11
GROUP BY regiao

 

coloca um OR dentro do where

WHERE times.numero_jogadores>11 OR qtd_equipas = 0

saída será as regiões com número de jogadores acima de 11 e as regiões onde o count será zero