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
),

DATA_TBL as(
select
    WEEK_NO
    ,round(avg(DAILY_CNT),0) as AVG_CNT
    --,round(avg(DAILY_AVG)) as AVG_AMT -> 0.0になるため末尾の.0を削除
    ,substr(round(avg(DAILY_AVG)),1,length(round(avg(DAILY_AVG)))-2) 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
    )
group by
    WEEK_NO
)

select
    W.WEEK
    ,ifnull(D.AVG_CNT,0) as AVG_CNT
    ,case 
    when length(D.AVG_AMT)>6 then
    substr(D.AVG_AMT,1,length(D.AVG_AMT)-6) || ','
    || substr(D.AVG_AMT,length(D.AVG_AMT)-5,3) || ','
    || substr(D.AVG_AMT,length(D.AVG_AMT)-2,3) || '円'
    when length(D.AVG_AMT)>3 then
    substr(D.AVG_AMT,1,length(D.AVG_AMT)-3) || ','
    || substr(D.AVG_AMT,length(D.AVG_AMT)-2,3) || '円'
    when length(D.AVG_AMT)>0 then
    D.AVG_AMT || '円' 
    else '0円'
    end as AVG_AMT
from
    DATA_TBL as D
    right outer join
    WEEK_TBL as W
    on D.WEEK_NO = W.WEEK_NO
order by
    W.WEEK_NO;