select 
case base.WEEK
when '0' then '日'
when '1' then '月'
when '2' then '火'
when '3' then '水' 
when '4' then '木' 
when '5' then '金' 
when '6' then '土'
end as WEEK,
ifnull(dt.AVG_CNT, 0) as AVG_CNT,
 case
  when  length(ifnull(dt.AVG_AMT, 0) ) > 6
     then substr(ifnull(dt.AVG_AMT, 0) ,1,length(ifnull(dt.AVG_AMT, 0) )-6) || ',' || substr(ifnull(dt.AVG_AMT, 0) ,length(ifnull(dt.AVG_AMT, 0) )-5,3) || ',' || substr(ifnull(dt.AVG_AMT, 0) ,length(ifnull(dt.AVG_AMT, 0) )-2,3)
  when  length(ifnull(dt.AVG_AMT, 0) ) > 3
     then substr(ifnull(dt.AVG_AMT, 0) ,1,length(ifnull(dt.AVG_AMT, 0) )-3) || ',' || substr(ifnull(dt.AVG_AMT, 0) ,length(ifnull(dt.AVG_AMT, 0) )-2,3)
  else  ifnull(dt.AVG_AMT, 0) 
 end || '円' as AVG_AMT
from  (
select '0' as WEEK
union all select '1'
union all select '2'
union all select '3'
union all select '4'
union all select '5'
union all select '6'
) base
left join (
select 
strftime('%w', SALES_DATE) as WEEK,
(count(1)+2)/4 as AVG_CNT,
(sum(SALES_AMT)+2)/4 as AVG_AMT
from sales
where SALES_DATE between '2024-03-01' and '2024-03-28'
and  SALES_TYPE != 2
and UPDATED_NO is null
group by strftime('%w', SALES_DATE)
) dt
on base.WEEK = dt.WEEK
;