Select by days of the month returning 0 when there is no record of that day

Question

I have a company call database, for example let’s say the table has only:

-- TABELA CHAMADO --
id (id do chamado)
inicio (data de inicio do chamado)
id_criador (id do usuário que abriu o chamado)
----------------------------------------------

I need to elaborate a select that tells me how many calls each user opened on each day of the month, returning 0 if he hasn’t opened any, for example.

id_criador      dia            qnt
1               2018-08-01     7
1               2018-08-02     0
1               2018-08-03     6

I have already found in other topics the command

generate_series(DATE'2018-08-01',DATE'2018-08-31',INTERVAL'1 day')

that returns to me every day of the month, but I am not able to join this result with the data of the so-called table. What would be the best way to do that?

Answer 1

That was the best solution I could find. I hope I can help you.

SELECT T2.id_criador, T2.dia, COALESCE(qnt, 0) AS qnt
FROM
   (
      SELECT inicio, COUNT(*) qnt, id_criador
      FROM chamado
      GROUP BY id_criador, inicio
   ) T1
   RIGHT JOIN
   (
      SELECT id_criador, dia
      FROM
      (
         SELECT date_trunc('day', dd) dia
         FROM
           generate_series(DATE'2018-08-01',DATE'2018-08-31',INTERVAL'1 day') dd
      ) AS d1 
      FULL JOIN
      (
          SELECT DISTINCT id_criador FROM  chamado C
          WHERE inicio BETWEEN '2018-08-01' AND '2018-08-31'
      ) AS D2
      ON 1=1
   ) T2
   ON T1.id_criador = T2.id_criador AND T1.inicio = T2.dia
ORDER BY id_criador, dia

Here is proof that my code is correct