MySQL: list of dates between a range with joined subselect

Question

I know from this answer that I can easily select a list of dates in MySQL.

select * from 
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2012-02-10' and '2012-02-15'

So far so good: now, for each selected_date, I'd like ie. to count the records in another table (say reports) where field reports_date matches selected_date, thus obtaining a recordset like this:

| selected_date | reports_count |
|---------------|---------------|
| 2012-02-10    | 12            |
| 2012-02-11    | 10            |
| 2012-02-12    | 3             |
| 2012-02-13    | 0             |
| 2012-02-14    | 9             |
| 2012-02-15    | 15            |

I tried to add the subquery select count(*) as reports_count from reports where reports_dated = selected_date somewhere, but with no luck

Please, any help? Thanks

As has presumably been mentioned before, consider handling issues of data display in application code — Strawberry, Jul 27 '19 at 10:03

score 1 · Accepted Answer · answered Jul 27 '19 at 08:57

You could use LEFT JOIN:

SELECT s.selected_date, COUNT(t2.reports_dated) AS reports_count
FROM (
select * 
from 
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2012-02-10' and '2012-02-15'
) s
LEFT JOIN table2 t2
  ON s.selected_date = t2.reports_dated
GROUP BY s.selected_date
ORDER BY selected_date;

MySQL: list of dates between a range with joined subselect

1 Answers1