Select return result even if there is no NULL record, grouped by day

Asked

Viewed 154 times

0

I have a select that returns a data set by DATA. Example: enter image description here

As you can see in the image below, there is no data for days: 20,21,22...28

I would like to bring those lines with result "NULLO"

My select is simple, the date column has date PER day

SELECT * 
from rentabilidade_fundos 
where data BETWEEN '2020-04-01' AND '2020-04-31' 
order by data
mysql sql

  • You cannot recover data that does not exist on your table. One solution is to use an auxiliary table with all days of the desired period and make a LEFT OUTER JOIN with your table using the date as the join criterion. If you also use the COALESCE function you can display what you want if the field is NULL.

  • see if it helps: https://answall.com/a/300026/69359

1 answer

1


You could generate the month data in an auxiliary table. As an example, da para fazer isso usando "tables" do próprio Mysql:

SELECT DATE_ADD('2020-04-01', INTERVAL rnk day) dia 
  FROM (SELECT row_number() over() -1 rnk 
          FROM information_schema.columns limit 31) gerado

Then just do one LEFT OUTER JOIN of this auxiliary table with your query:

WITH dias AS (
SELECT DATE_ADD('2020-04-01', INTERVAL rnk DAY) dia 
  FROM (SELECT row_number() over() -1 rnk 
          FROM information_schema.columns LIMIT 31) gerado)


SELECT dias.dia, r.fundo_conservador, r.fundo_moderado, r.fundo_arrojado
  FROM dias
  LEFT OUTER JOIN rentabilidade_fundos r
    ON r.data = dias.dia
 WHERE dias.dia BETWEEN '2020-04-01' AND '2020-04-31' 
 ORDER BY dias.dia

See spinning here.

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.