En este artículo de consultas con MySQL te enseñaré un ejemplo y la sintaxis de cómo unir dos tablas pero para la segunda tabla obtener el conteo agrupado por un ID usando COUNT.

Básicamente será un ejemplo de count con inner join en MySQL usando subconsultas; y al final verás que de hecho no vamos a usar inner join, bastará con la subquery.

Sintaxis básica

Si solo vienes por la sintaxis, puede ser:

SELECT tabla1.valor, tabla1.otro_valor
(SELECT COUNT(*) FROM tabla2
WHERE tabla1.valor = tabla2.otro_valor) conteo
FROM tabla1

Lo que estamos haciendo es un select normal como lo haríamos de la tabla, pero luego, dentro de los paréntesis, usamos una subconsulta en donde hacemos un SELECT COUNT en la otra tabla, comparando el valor de la tabla 1 con el valor de la otra tabla (de este modo se obtienen conteos agrupados) y finalmente le asignamos un alias que en este caso es conteo.

Ejemplo

Para explicar de mejor manera, te mostraré un ejemplo de count con tabla externa en MySQL. Yo tengo 2 tablas, una de usuarios y otra que indica cuántas veces han fallado al querer iniciar sesión.

Yo puedo saber cuántos conteos fallidos llevan contando cuántas filas hay de ese usuario:

MariaDB [login]> describe usuarios; describe intentos_usuarios; select * from usuarios; select * from intentos_usuarios;
+-----------------+---------------------+------+-----+---------+----------------+                                       
| Field           | Type                | Null | Key | Default | Extra          |                                       
+-----------------+---------------------+------+-----+---------+----------------+                                       
| id              | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |                                       
| correo          | varchar(255)        | NO   |     | NULL    |                |                                       
| palabra_secreta | varchar(255)        | NO   |     | NULL    |                |                                       
+-----------------+---------------------+------+-----+---------+----------------+                                       
3 rows in set (0.014 sec)                                                                                               
                                                                                                                        
+------------+---------------------+------+-----+---------+-------+                                                     
| Field      | Type                | Null | Key | Default | Extra |                                                     
+------------+---------------------+------+-----+---------+-------+                                                     
| id_usuario | bigint(20) unsigned | NO   | MUL | NULL    |       |                                                     
+------------+---------------------+------+-----+---------+-------+                                                     
1 row in set (0.012 sec)                                                                                                
                                                                                                                        
+----+---------------------+-----------------+                                                                          
| id | correo              | palabra_secreta |                                                                          
+----+---------------------+-----------------+                                                                          
|  1 | parzibyte@gmail.com | 123             |                                                                          
|  2 | luis@gmail.com      | 123             |                                                                          
+----+---------------------+-----------------+                                                                          
2 rows in set (0.000 sec)                                                                                               
                                                                                                                        
+------------+                                                                                                          
| id_usuario |                                                                                                          
+------------+                                                                                                          
|          1 |                                                                                                          
|          1 |                                                                                                          
|          2 |                                                                                                          
+------------+                                                                                                          
3 rows in set (0.000 sec)                                                                                               
                                                                                                                        
MariaDB [login]>                                                                                                        

Si te fijas, en este caso el usuario con el id 1 ya lleva 2 intentos fallidos, pues su id aparece dos veces en la tabla intentos_usuarios. Yo puedo hacer un count(*) de esa tabla usando un where, pero si quiero la lista completa de usuarios junto con el conteo, hago esto:

SELECT usuarios.id, usuarios.correo,
(SELECT COUNT(*) FROM intentos_usuarios WHERE id_usuario = usuarios.id)intentos_fallidos
FROM usuarios

Lo que me da los detalles de los usuarios junto con el conteo de la otra tabla:

MariaDB [login]> SELECT usuarios.id, usuarios.correo,
    -> (SELECT COUNT(*) FROM intentos_usuarios WHERE id_usuario = usuarios.id)intentos_fallidos
    -> FROM usuarios;
+----+---------------------+-------------------+
| id | correo              | intentos_fallidos |
+----+---------------------+-------------------+
|  1 | parzibyte@gmail.com |                 2 |
|  2 | luis@gmail.com      |                 1 |
+----+---------------------+-------------------+
2 rows in set (0.000 sec)

MariaDB [login]>

Y de este modo sin usar inner join pude unir las dos tablas o mejor dicho, obtener datos agrupados de la segunda tabla, gracias a las subconsultas.

Si quieres puedes leer más sobre MySQL en mi blog.