(Resolvido) Listar DIAS em sequência com e seu TOTAL de registros

[Helder Mozart]

Estou tentando mostrar os dias (em sequencia) e a quantidade de um tipo de registros gravados naquele dia. 

Consegui escrever um select onde tenho os dias sequenciados MAS quando o dia não tem registros do tipo específico este dia não aparece no resultado

 

SELECT tipo, COUNT(*) AS total, DATE_FORMAT(data_registro, '%d') AS dia
FROM tbl_tabela
WHERE data_registro
BETWEEN '2019-11-01'
AND '2019-11-31' 
AND tipo  = '1'
GROUP BY dia

resultado

dia - total
01  -10
02 - 4
03 - 19
06 - 3
07 - 27

 eu precisava listar também os dias que o resultado fosse ZERO registros sem pular dias.

dia total
01 - 10
02 - 4
03 - 19
04 - 0
05 - 0
06 - 3
07 - 27

também TENTEi o select abaixo, mas obtive o mesmo resultado

SELECT DAY(data_registro) dia, 
    COUNT(*)
FROM
    tbl_tabela
WHERE
    MONTH(data_registro) = 11 AND tipo = '1' 
GROUP BY dia
ORDER BY dia

😞

Alguém pode me dá uma dica de como listar também os dias sem registros informando como valor 0?

 

AGRADEÇO A GENTILEZA, AMIGOS!

 

[Leonardo Persan]

O correto é ter duas tabelas distintas uma para os dias e outra para os registros de fato com um relacionamento entre elas e fazer o LEFT JOIN, mas da pra simular isso na consulta.

SELECT DATE_FORMAT(d.data_registro, '%d') AS dia, t.tipo, COUNT(t.tipo) AS total
FROM (SELECT DISTINCT data_registro FROM tbl_tabela WHERE data_registro BETWEEN '2019-11-01' AND '2019-11-31') AS d
LEFT JOIN tbl_tabela AS t ON d.data_registro = t.data_registro
WHERE t.tipo = 1
GROUP BY t.data_registro

Estou assumindo que o campo data_registro é do tipo DATE se ele for DATETIME você vai precisar usar um DATE_FORMAT

Editado Novembro 4, 2019 por Leonardo Persan

[Helder Mozart]

Obrigado pela ajuda Leonardo Persan, mas ao executar seu código tenho o mesmo resultado que antes: me vem apenas os dias com registro... O que será que está havendo?

[Leonardo Persan]

Isso significa que na tabela existem dias que não têm nenhum registro nem do tipo 1 e nem de outros tipos porque a consulta:

SELECT DISTINCT data_registro FROM tbl_tabela WHERE data_registro BETWEEN '2019-11-01' AND '2019-11-31'

Seleciona todas as datas cadastradas.

Você precisa fazer isso pelo MySQL mesmo? Essa consulta é executada por algum script seu? Qual é a linguagem de programação?

[Helder Mozart]

 

Leonardo, primeiro, obg pela resposta.

Bem, fiz uma select geral em duas datas próximas para ver os registros entre elas

depois executei o SELECT que você indicou 

No entanto, como pode ver, não foram listados todos os dias e sim apenas os com o tipo definido. 

USO PHP como linguagem pra tratar os dados 

 

[AlexandreNSilva]

Bom dia!

Verificando o codigo esta trazendo no resultado do WHERE somente os registros que possuem a condição id_auth = '00.0000.0000'

você pode colocar esta condição no ON para unir essas duas tabelas, ou utilizar um CASE WHEN para somar quando houver essa condição.

SUM(CASE WHEN id_auth = '00.0000.000' THEN 1 ELSE 0 END)[TOTAL]

mas acredito que assim deva resolver:

SELECT DATE_FORMAT(d.data_exame, %d) AS dia, t.id_auth, COUNT(t.id_auth) AS TOTAL
FROM
    (
        SELECT DISTINCT data_exame
        FROM tbl_medreq_hospital_exame
        WHERE data_exame BETWEEN '2019-11-02' AND '2019-11-04'    
    ) AS d
        LEFT JOIN
    (SELECT id_auth FROM tbl_medreq_hospital_exame WHERE id_auth = '00.0000.0000') AS t
        ON d.data_exame = t.data_exame
GROUP BY t.data_exame
LIMIT 0,30

Editado Novembro 5, 2019 por AlexandreNSilva

[Helder Mozart]

AlexandreNSilva, deu o erro Unknown column 't.data_exame' in 'on clause'

Os dados estão numa tabela só.

A ideia é que venha todos os dias no intervalo de datas e quando o dia não tiver a condição vir com 0.

A SAÍDA PRETENDIDA É:

dia - total

02   -  1

03   -  0

04   -  1

[AlexandreNSilva]

é..eu esqueci de colocar a coluna t.data_exame dentro do select.

 

SELECT DATE_FORMAT(d.data_exame, %d) AS dia, t.id_auth, COUNT(t.id_auth) AS TOTAL
FROM
    (
        SELECT DISTINCT data_exame
        FROM tbl_medreq_hospital_exame
        WHERE data_exame BETWEEN '2019-11-02' AND '2019-11-04'    
    ) AS d
        LEFT JOIN
    (SELECT id_auth, data_exame FROM tbl_medreq_hospital_exame WHERE id_auth = '00.0000.0000') AS t
        ON d.data_exame = t.data_exame
GROUP BY t.data_exame
LIMIT 0,30

[Helder Mozart]

Obrigado AlexandreNSilva, seu select aparentemente tá funcionando, vou tentar em uma faixa de datas maiores e com outras condicionais.

Obrigado também a Leonardo Persan pelo apoio.

vocês são 10!

Felicidades e até outra vez!!