Count number of rows of different tables doing LEFT JOIN using the Count() function

Question

I need to count the number of tables records cliques and curtidas and show these numbers to the media type Video table tipo_midia and the specific media, that is, the video that the person liked or clicked, from the table videos.

videos
+--+----------+--------------+
|id| titulo   |id_tipo_midia |
+-------------+--------------+
|01| video_01 | 01           |
|02| video_02 | 01           |
|03| video_03 | 01           |
|04| video_04 | 01           |
+-------------+--------------+

cliques
+--------------+--------+------------+
|id_tipo_midia |id_midia| data_acesso| 
+--------------+--------+------------+
|01            | 03     | 2019-06-03 |
|01            | 03     | 2019-06-03 |
|03            | 01     | 2019-06-05 |
+--------------+--------+------------+

curtidas
+--------------+--------+------------+
|id_tipo_midia |id_midia| data_acesso| 
+--------------+--------+------------+
|01            | 03     | 2019-06-03 |
|01            | 03     | 2019-06-03 |
|01            | 01     | 2019-06-05 |
+--------------+--------+------------+

tipo_midia
+--+-------+
|id| tipo  |
+----------+
|01| Vídeo |
|02| Audio |
|03| Imagem|
+----------+

My query:

select tipo_midia.tipo, videos.titulo, count(cliques.id_midia), count(curtidas.id_midia) from tipo_midia
left join videos on (tipo_midia.id = videos.id_tipo_midia)
left join cliques on (videos.id = cliques.id_midia)
left join curtidas on (videos.id = curtidas.id_midia)
where tipo_midia.id = '1'
group by 1, 2

The query must return me:

*---------*---------*--------*---------*
|tipoMidia| titulo  | cliques| curtidas|
*---------*---------*--------*---------*
|video    |video_03 | 2      | 2       |
|video    |video_01 | 1      | 0       |
*---------*---------*--------*---------*

Answer 1

You can use a sub-query:

select 
    tipo_midia.tipo, 
    videos.titulo, 
    (select count(cliques.id_midia) from cliques where cliques.id_midia = videos.id) as cliques, 
    (select count(curtidas.id_midia) from curtidas where curtidas.id_midia = videos.id) as curtidas
from tipo_midia
inner join videos on (tipo_midia.id = videos.id_tipo_midia)

Answer 2

Some possible errors in your table structure:

• Why a table videos if you already have a related table tipo_midia?
• Why repeat id_tipo_ midia in the tables cliques and curtidas?
• If you do not use time, user, etc, why not make a cumulative by date?

---

Under these conditions, the solution I would first use would be to simplify the tables:

Table videos will turn registros, because you have a table tipo_midia that identifies the file type:

registros
+--+----------+--------------+
|id| titulo   |id_tipo_midia |
+-------------+--------------+
|01| vid_01   |     01       |
|02| vid_02   |     01       |
|03| img_01   |     03       |
|04| aud_01   |     02       |
+-------------+--------------+

Table cliques and curtidas, will be the same table, with an accumulated by data_acesso, thus save much unnecessary registration, since you do not link user or time to click:

cliques_curtidas
+--------------+--------+--------+------------+
|id_midia      |cliques |curtidas| data_acesso| 
+--------------+--------+--------+------------+
|01            | 95     | 65     | 2019-06-03 |
|01            | 17     | 11     | 2019-06-04 |
|02            | 59     | 37     | 2019-06-03 |
+--------------+--------+--------+------------+

Table tipo_midia keeps:

tipo_midia
+--+-------+
|id| tipo  |
+----------+
|01| Vídeo |
|02| Audio |
|03| Imagem|
+----------+

---

Later, the most you’d have to do is a SUM for id_midia table cliques_curtidas:

SELECT tipo, titulo, SUM(cliques), SUM(curtidas)
FROM cliques_curtidas cc
LEFT JOIN tipo_midia tm ON tm.id = cc.id_tipo_midia 
GROUP BY id_midia