Проблема с отображением фильтра данных по дате в MySQL

создать дату и присоединиться к таблице заказов

    select SUM(O.total_amount) as total, DATE(gen_date) as oreder_date
        from    
        (
         select * from 
            (select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from
             (select 0 t0 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 t1 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 t2 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 t3 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 t4 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 gen_date between '2018-09-01' and '2018-09-30'
        ) as t1 left join `Order` O on t1.gen_date= DATE(O.date_order)

where gen_date >= '2018-09-01' and gen_date <= '2018-09-06'
         group by oreder_date

Создайте еще один стол, чтобы присоединиться к нему.

Это может быть таблица дат, например все даты с 01.01.2000 по 31.12.2099 ...

SELECT
  dt.`date`,
  SUM(total_amount) as `total`
FROM
  yourDatesTable     AS dt
LEFT JOIN
  Order              AS o
    ON  o.`date_order` = dt.`date`
WHERE
      dt.`date` >= '2018-09-01'
  AND dt.`date` <  '2018-09-08'
GROUP BY
  dt.`date`
ORDER BY
  dt.`date`

Или это может быть таблица чисел со значениями от -1023 до +1024 (или другой полезный диапазон) ...

SELECT
  n.id,
  '2018-09-01' + INTERVAL n.id DAYS,
  SUM(total_amount) as `total`
FROM
  yourNumbersTable   AS n
LEFT JOIN
  Order              AS o
    ON  o.`date_order` = '2018-09-01' + INTERVAL n.id DAYS
WHERE
      n.id >= 0
  AND n.id <  8
GROUP BY
  n.id
ORDER BY
  n.id