Count in two tables

Question

Count in two tables

Navigation

#1 by (3 votes)

4

I'm having trouble counting neighborhood items. I need to list all of the neighborhoods in City X and show the available cars and motorcycles items. In the example I just used a city for easy illustration. I used group by and left join but did not count correctly.

I do not know if the error is mine, or if there is a difficulty in using join in this case, but some solutions I found suggest something like the query below:

Ref 1

SELECT
  (SELECT COUNT(*) FROM table1 WHERE someCondition) as table1Count, 
  (SELECT COUNT(*) FROM table2 WHERE someCondition) as table2Count,
  (SELECT COUNT(*) FROM table3 WHERE someCondition) as table3Count

Ref 2

SELECT co.*, 
    (SELECT COUNT(*) FROM modules mod WHERE mod.course_id=co.id) AS moduleCount, 
    (SELECT COUNT(*) FROM videos vid WHERE vid.course_id=co.id) AS vidCount
FROM courses AS co
ORDER BY co.id DESC

The references above make the condition very repetitive. Does join meet this need?

table

TBL.BAIRROS
ID | CIDADE | BAIRRO
1  | 1      | Meier
2  | 1      | Copacabana

TBL.CARROS
ID | BAIRRO | MODELO
1  | 1      | Modelo 1
2  | 1      | Modelo 2

TBL.MOTOS
ID | BAIRRO | MODELO
1  | 2      | Modelo 1

Expected output

array(
    0 => array
         bairro => Meier
         carros => 2
         motos  => 0

    1 => array
         bairro => Copacabana
         carros => 0
         motos  => 1
)

using left join

...
, count( carros.id ) carros
, count( motos.id  ) motos
from bairros
left join motos  on motos.bairro  = bairros.id
left join carros on carros.bairro = bairros.id
group by bairros.bairro

Update

While writing I found a possible solution using count( DISTINCT ... ) . The output came out as expected, but I wanted to know if the solution only circumvents a query problem or if it is the correct form.

...
, count( DISTINCT carros.id ) carros
, count( DISTINCT motos.id  ) motos
from bairros
left join motos  on motos.bairro  = bairros.id
left join carros on carros.bairro = bairros.id
group by bairros.bairro

mysql join count distinct

asked by anonymous 31.01.2015 / 22:38

1 answer

Map method does not work as expected Show session time remaining

score 3 · Answer 1

Try JOIN with COUNT defining the field that will be counted because it ignores the null registers.

SELECT
  bairro,
  COUNT(idCarro) AS qtde_carros,
  COUNT(idMoto) AS qtde_motos
FROM bairro b
  LEFT JOIN carros c ON (c.idBairro = b.idBairro)
  LEFT JOIN motos m ON (m.idBairro = b.idBairro)
GROUP BY bairro