Generate a last 7 days weekly report in Mysql

I want to get a report of my eco_orders table that how much order i get in a day for last 7days. I got the result with below sql and it’s absolutely fine. but the problem is I want to include the non order day count with zero

SELECT DAYNAME(created_at) AS DAY,  count(*) 
FROM `eco_orders` 
WHERE created_at >= DATE(NOW()) - INTERVAL 7 DAY 
GROUP BY DAY;

my output
+-----------+----------+
| DAY       | count(*) |
+-----------+----------+
| Thursday  |        1 |
| Wednesday |        2 |
+-----------+----------+

expected output
+-----------+----------+
| DAY       | count(*) |
+-----------+----------+
| Saturday  |        0 |
| Sunday    |        0 |
| Monday    |        0 |
| Tuesday   |        0 |
| Wednesday |        2 |
| Thursday  |        1 |
| Friday    |        0 |
+-----------+----------+

Answer

You can try this option –

select t.d,cnt from
(
select 'Saturday' as d union all
select 'Sunday' union all
select 'Monday' union all
select 'Tuesday' union all
select 'Wednesday' union all
select 'Thursday' union all
select 'Friday'
)t left join
(
SELECT DAYNAME(created_at) AS DAY,  count(*) as cnt 
FROM `eco_orders` 
WHERE created_at >= DATE(NOW()) - INTERVAL 7 DAY 
GROUP BY DAY
)t1 on t.d=t1.day