with base as (

select
strftime('%w', SALES_DATE) as WEEK
 , 1 as SALES_CNT
 , SALES_AMT
from
    SALES as main
where
    main.SALES_TYPE <> 2
   and main.UPDATED_NO is null
union all select '0', 0, 0
union all select '1', 0, 0
union all select '2', 0, 0
union all select '3', 0, 0
union all select '4', 0, 0
union all select '5', 0, 0
union all select '6', 0, 0
)
, base2 as (
select
WEEK as WEEK2, 
      case when WEEK = '0' then '日'
          when WEEK = '1' then '月'
          when WEEK = '2' then '火'
          when WEEK = '3' then '水'
          when WEEK = '4' then '木'
          when WEEK = '5' then '金'
          when WEEK = '6' then '土'
          else null
     end as WEEK
 , cast(round(total(SALES_CNT) / 4) as int) as AVG_CNT
 , cast(round(total(SALES_AMT) / 4) as int) as AVG_AMT
from
    base as main
group by
      main.WEEK
)
select WEEK,
 AVG_CNT,
 case
  when  length(AVG_AMT) > 6
     then substr(AVG_AMT,1,length(AVG_AMT)-6) || ',' || substr(AVG_AMT,length(AVG_AMT)-5,3) || ',' || substr(AVG_AMT,length(AVG_AMT)-2,3)
  when  length(AVG_AMT) > 3
     then substr(AVG_AMT,1,length(AVG_AMT)-3) || ',' || substr(AVG_AMT,length(AVG_AMT)-2,3)
  else  AVG_AMT
 end || '円' AS AVG_AMT
from
    base2 as main
order by
     main.WEEK2