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

Question

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

Navigation

#1 by (1 votes)

5

I have a database of company calls, for example let's say that 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 compile a select that tells me how many calls each user opened each day of the month, returning 0 if he did not open any, for example.

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

I already found in other threads the command

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

that returns me every day of the month, but I'm not able to merge this result with the data in the called table. What would be the best way to do this?

sql postgresql

asked by anonymous 05.09.2018 / 17:03

1 answer

Send and pick GET by AMIGAVEL URL How to clone just a subdirectory of github

score 1 · Accepted Answer

This was the best solution I found. 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