1
Example
select data, valor from tabela WHERE data BETWEEN '2020-01-01' AND '2020-01-10'.
I need that when there is no day 5, for example, the query return me the 10 dates and on day 5 the value is equal to zero.
SELECT DATA, VALOR FROM `TABELA` WHERE data BETWEEN '2020-04-01' AND '2020-04-10' ORDER BY DATA ASC 0
This will require a JOIN between your data and a "table" with all dates. To avoid creating this table, you can use CTE. To have the value 0 where there is no value, you can use the COALESCE. So your query would look something like
WITH recursive dias AS (
select '2020-04-01' as data
union all
select data + interval 1 day
from dias
where data < '2020-04-10')
SELECT dias.data, COALESCE(valor, 0)
FROM dias
LEFT JOIN tabela on dias.data = tabela.data
ORDER BY dias.data ASC
It is possible to test this example in fiddle https://www.db-fiddle.com/f/ctZk4hTXnGJt8TFwm32bER/2.
tvdias, I thank you immensely, not only for having solved my problem, but for having devoted your time to help me!
And you also introduced me to a new tool, which until then I was unaware of.
Browser other questions tagged mysql query select
You are not signed in. Login or sign up in order to post.
vc did not say the BD , what I do in these cases (Oracle is simple) I do a sequence of dates with zero values and do the sum see if it helps https://forum.imasters.com.br/topic/522521-for%C3%A7ar-select-bring-line-same-as-value/ https://en.stackoverflow.com/questions/446579/sql-records-0/446717#446717
– Motta
My comic is phpmyadmin
– user181663
Mysql https://stackoverflow.com/questions/14105018/generating-a-series-of-dates https://dba.stackexchange.com/questions/224182/generate-dates-between-date-ranges-in-mysql
– Motta