죽 나열해서 dayofweek 써봤습니다~

select sum(case when dayofweek(dt) = 1 then 1 else 0 end) sunday,
       sum(case when dayofweek(dt) = 2 then 1 else 0 end) monday,
       sum(case when dayofweek(dt) = 3 then 1 else 0 end) tuesday,
       sum(case when dayofweek(dt) = 4 then 1 else 0 end) wednesday,
       sum(case when dayofweek(dt) = 5 then 1 else 0 end) thursday,
       sum(case when dayofweek(dt) = 6 then 1 else 0 end) friday,
       sum(case when dayofweek(dt) = 7 then 1 else 0 end) saturday       
from (
select 
    date_format(
        adddate('2021-1-1', @num:=@num+1), 
        '%Y-%m-%d'
    ) dt
from 
    (select * from seq_1_to_365) dummy,    
    (select @num:=-1) num
limit 
    365
) as cal
where dt between '2021-06-29' and '2021-06-30'