Perform two COUNT with INNER JOIN

Question

When using the SQL below to carry out the count of the number of brokers in a table using INNER JOIN, works perfectly.

SQL:

SELECT
    conta.acesso,
    count(corretor.cod) as num_corr
    FROM
    conta
    INNER JOIN corretor
    ON conta.id = corretor.cod
    where conta.id = '1015'

However, when trying to include the count also of the user number, then everything stops working. See the SQL down below:

SELECT
    conta.acesso,
    count(corretor.cod) as num_corr,
    count(usuarios.cliente) as num_user
    FROM
    conta
    INNER JOIN corretor
    ON conta.id = corretor.cod
    INNER JOIN usuarios
    ON conta.id = usuarios.cliente
    where conta.id = '1015'

In this SQL just included to count the users table and nothing else works.

Answer 1

Try these two ways, maybe it’ll help you or a light:

SELECT co.acesso
      ,c.num_corr
      ,u.num_user
  FROM conta co
 LEFT JOIN (select cod, count(cod) as num_corr from corretor)         c ON co.id = c.cod
 LEFT JOIN (select cliente, count(cliente) as num_user from usuarios) u ON co.id = u.cliente
 WHERE co.id = '1015'

OR

SELECT co.acesso
      ,count(c.cod)     as num_corr
      ,count(u.cliente) as num_user
  FROM conta co
 LEFT JOIN corretor c ON c.cod     = co.id
 LEFT JOIN usuarios u ON u.cliente = co.id 
 WHERE co.id = '1015'
 GROUP BY co.acesso

EDITED

SELECT co.acesso
      ,c.num_corr
      ,u.num_user
  FROM conta co
 LEFT JOIN (select cod, count(cod) as num_corr from corretor group by cod)             c ON co.id = c.cod
 LEFT JOIN (select cliente, count(cliente) as num_user from usuarios group by cliente) u ON co.id = u.cliente
 WHERE co.id = '1015'
 GROUP BY co.acesso