select base.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
;