Perform two COUNT with INNER JOIN

Question

Perform two COUNT with INNER JOIN

Navigation

#1 by (1 votes)

0

By using the SQL below to count the number of brokers in a table using INNER JOIN , it 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 the user number, everything stops working. See SQL 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 I only include to count the users table and nothing else works.

mysql sql inner-join

asked by anonymous 13.07.2018 / 16:38

1 answer

Draw vertical line centered on image [closed] Javascript Prompt

score 1 · Accepted Answer

Try these two ways, maybe 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