Question:
How to show in a sql query, value 0 when there is no data in the table?
SELECT
ae.dt_data,
to_char(ae.dt_data, 'dy'),
case
when to_char(ae.dt_data, 'dy') = 'sun' then 'Domingo'
when to_char(ae.dt_data, 'dy') = 'mon' then 'Segunda'
when to_char(ae.dt_data, 'dy') = 'tue' then 'Terça'
when to_char(ae.dt_data, 'dy') = 'wed' then 'Quarta'
when to_char(ae.dt_data, 'dy') = 'thu' then 'Quinta'
when to_char(ae.dt_data, 'dy') = 'fri' then 'Sexta'
when to_char(ae.dt_data, 'dy') = 'sat' then 'Sabado'
end as dia_semana,
count(DISTINCT ae.nr_controle) as qtde
FROM atendimentos ae
join exames ex using(cd_atendimento)
join procedimentos pr using(cd_procedimento)
join modalidades mo using(cd_modalidade)
join pacientes pa using(cd_paciente)
join salas sa using(cd_sala)
join medicos me on me.cd_medico = ae.cd_medico
join planos pl on pl.cd_plano = ex.cd_plano
join empresas em on sa.cd_empresa = em.cd_empresa left
join medicos ms on ms.cd_medico = ex.cd_medico left
join atendimentos_localizacao al on al.cd_localizacao = ae.cd_localizacao
WHERE ae.dt_data >= now() :: date - 7 AND ae.dt_data < now() :: date
and sa.cd_empresa = 7
and ae.cd_sala in (74, 4, 121, 6, 7, 8, 75, 122, 91, 11, 111, 12, 134)
and pr.cd_modalidade in (40, 14, 12, 19, 18, 15, 38, 17, 33, 20, 1, 16, 64)
and pr.cd_procedimento in (627, 628, 631, 629, 635, 634, 630, 633, 632, 636, 669, 915, 604, 620, 616, 606, 608, 625, 610, 646, 828, 770, 614, 1013, 735, 618, 826)
and ae.nr_controle is not null -- Descarta pacientes que não foram atendidos (Roxo ou Preto)
group by dt_data
In this query above, he provides me with this data:
Dia_semana | qtde
Quinta | 57
Sexta | 64
Sabado | 1
Segunda | 80
Terça | 64
Quarta | 54
As Domingo had no data to be consulted, he did not show it in the result. I would like Sunday = 0, like the example below for days of the week that do not have data.
Dia_semana | qtde
Quinta | 57
Sexta | 64
Sabado | 1
Domingo | 0
Segunda | 80
Terça | 64
Quarta | 54
I've already tried to consult using coalesce and I couldn't.
The bank is Postgres.
Answer:
Making a left outer join with a fake table should solve…
WITH vazio as
(
SELECT NOW() dt_data UNION ALL
SELECT NOW() - INTERVAL '1 day' dt_data UNION ALL
SELECT NOW() - INTERVAL '2 day' dt_data UNION ALL
SELECT NOW() - INTERVAL '3 day' dt_data UNION ALL
SELECT NOW() - INTERVAL '4 day' dt_data UNION ALL
select NOW() - INTERVAL '5 day' dt_data UNION ALL
SELECT NOW() - INTERVAL '6 day' dt_data
)
select v.dt_data,
case
when to_char(v.dt_data, 'dy') = 'sun' then 'Domingo'
when to_char(v.dt_data, 'dy') = 'mon' then 'Segunda'
when to_char(v.dt_data, 'dy') = 'tue' then 'Terça'
when to_char(v.dt_data, 'dy') = 'wed' then 'Quarta'
when to_char(v.dt_data, 'dy') = 'thu' then 'Quinta'
when to_char(v.dt_data, 'dy') = 'fri' then 'Sexta'
when to_char(v.dt_data, 'dy') = 'sat' then 'Sabado'
end as dia_semana,
count(DISTINCT ae.nr_controle) as qtde
from vazio v
left outer join atendimentos ae
on cl.dat_err :: date = v.dt_data :: date
join exames ex using(cd_atendimento)
join procedimentos pr using(cd_procedimento)
join modalidades mo using(cd_modalidade)
join pacientes pa using(cd_paciente)
join salas sa using(cd_sala)
join medicos me
on me.cd_medico = ae.cd_medico
join planos pl
on pl.cd_plano = ex.cd_plano
join empresas em
on sa.cd_empresa = em.cd_empresa
left outer join medicos ms
on ms.cd_medico = ex.cd_medico left
join atendimentos_localizacao al on al.cd_localizacao = ae.cd_localizacao
WHERE ae.dt_data >= now() :: date - 7 AND ae.dt_data < now() :: date
and sa.cd_empresa = 7
and ae.cd_sala in (74, 4, 121, 6, 7, 8, 75, 122, 91, 11, 111, 12, 134)
and pr.cd_modalidade in (40, 14, 12, 19, 18, 15, 38, 17, 33, 20, 1, 16, 64)
and pr.cd_procedimento in (627, 628, 631, 629, 635, 634, 630, 633, 632, 636, 669, 915, 604, 620, 616, 606, 608, 625, 610, 646, 828, 770, 614, 1013, 735, 618, 826)
and ae.nr_controle is not null -- Descarta pacientes que não foram atendidos (Roxo ou Preto)
group by v.dt_data :: date
PS: As you don't have the script of the tables, I haven't tested it with this query. But I believe it will work too.