Count twice the number of records from one column to different values using the COUNT(*) function in a single query

Asked

Viewed 37 times

3

I need to count the number of hits and likes per user for different types of content.

SCHEMA:

secao
+--+--------+
|id| nome   |
+-----------+
|01| Vídeo  |
|02| Audio  |
|03| Imagem |
|04| Leitura|
+-----------+

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

midia
+--+---------------+----------+
|id| id_tipo_midia | titulo   |
+--+---------------+----------+
|01| 02            | audio_01 |
|02| 01            | video_03 |
|03| 03            | imagem_02|
|04| 02            | audio_02 |     
|05| 01            | video_04 |   
|06| 04            | pdf_01   |
+--+---------------+----------+

users
+--+---------+---------+
|id| nome    | email   |
+--+---------+---------+
|01| user_01 | email_01| 
|02| user_02 | email_02|
|03| user_03 | email_03|
|04| user_04 | email_04|
+--+----------+--------+

registro
+-------+---------+------+----------+
|id_user|id_midia | acao |data_acao |
+-------+---------+------+----------+
|01     | 01      | 1    |2019-07-27|
|01     | 01      | 1    |2019-07-27|
|01     | 02      | 2    |2019-07-27|
|02     | 01      | 1    |2019-07-27|
|03     | 03      | 2    |2019-07-27|
|03     | 03      | 1    |2019-07-27|
|02     | 04      | 1    |2019-07-27|
|01     | 06      | 2    |2019-07-27|
+-------+---------+------+----------+

On the table registro, the column acao when 1 means acesso and when it is 2 means curtida.

My query: 

SELECT U.nome AS usuario, U.email, M.titulo AS _midia, T.tipo, S.nome AS _secao, 
COUNT(A.`type`=1) AS acessos, COUNT(A.`type`=2) AS curtidas, MAX(A.`data`) ultima_data 
FROM registro A
INNER JOIN user U ON (A.id_user = U.id)
INNER JOIN midia M ON (A.id_midia = M.id)
INNER JOIN tipo_midia T ON (M.id_tipo_midia = T.id)
INNER JOIN secao S ON (T.id_secao = S.id)
GROUP BY 1, 3
sql postgresql

1 answer

1


Utilize CASE:

SELECT U.nome AS usuario, U.email, M.titulo AS _midia, T.tipo, S.nome AS _secao, 
COUNT(CASE WHEN A.`type` = 1 THEN 1 END) AS acessos, COUNT(CASE WHEN A.`type` = 2 THEN 1 END) AS curtidas, MAX(A.`data`) ultima_data 
FROM registro A
INNER JOIN user U ON (A.id_user = U.id)
INNER JOIN midia M ON (A.id_midia = M.id)
INNER JOIN tipo_midia T ON (M.id_tipo_midia = T.id)
INNER JOIN secao S ON (T.id_secao = S.id)
GROUP BY 1, 3

Browser other questions tagged sql postgresql

You are not signed in. Login or sign up in order to post.