with WEEK_TBL as(
select '0' as WEEK_NO, '日' as WEEK
union select '1' as WEEK_NO, '月' as WEEK
union select '2' as WEEK_NO, '火' as WEEK
union select '3' as WEEK_NO, '水' as WEEK
union select '4' as WEEK_NO, '木' as WEEK
union select '5' as WEEK_NO, '金' as WEEK
union select '6' as WEEK_NO, '土' as WEEK
)
select
    W.WEEK
    ,ifnull(round(cast(sum(DAILY_CNT) as real)/4),0) as AVG_CNT
    ,case 
    when length(cast(round(cast(sum(DAILY_AVG) as real)/4) as string))>8 then
    substr(cast(round(cast(sum(DAILY_AVG) as real)/4) as string),1,length(cast(round(cast(sum(DAILY_AVG) as real)/4) as string))-8) || ','
    || substr(cast(round(cast(sum(DAILY_AVG) as real)/4) as string),length(cast(round(cast(sum(DAILY_AVG) as real)/4) as string))-7,3) || ','
    || substr(cast(round(cast(sum(DAILY_AVG) as real)/4) as string),length(cast(round(cast(sum(DAILY_AVG) as real)/4) as string))-4,3) || '円'
    when length(cast(round(cast(sum(DAILY_AVG) as real)/4) as string))>5 then
    substr(cast(round(cast(sum(DAILY_AVG) as real)/4) as string),1,length(cast(round(cast(sum(DAILY_AVG) as real)/4) as string))-5) || ','
    || substr(cast(round(cast(sum(DAILY_AVG) as real)/4) as string),length(cast(round(cast(sum(DAILY_AVG) as real)/4) as string))-4,3) || '円'
    when length(cast(round(cast(sum(DAILY_AVG) as real)/4) as string))>2 then
    substr(cast(round(cast(sum(DAILY_AVG) as real)/4) as string),1,length(cast(round(cast(sum(DAILY_AVG) as real)/4) as string))-2) || '円' 
    else '0円'
    end as AVG_AMT
from (
    select
        strftime('%w',SALES_DATE) as WEEK_NO
        ,count(*) as DAILY_CNT
        ,avg(SALES_AMT) as DAILY_AVG
    from
        SALES
    where
        SALES_DATE between '2024-03-01' and '2024-03-28'
        and SALES_TYPE<>2
        and UPDATED_NO is null
    group by
        SALES_DATE
    ) as SUB
    right outer join
    WEEK_TBL as W
    on W.WEEK_NO=SUB.WEEK_NO
group by
    SUB.WEEK_NO