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
        strftime('%w',SALES_DATE) as WEEK_NO
        ,count(SALES_NO) as CNT_ORDER
        ,round(avg(SALES_AMT),0) as AVG_AMT
    from
        SALES
    where
        SALES_TYPE <> 2
        and UPDATED_NO is null
    group by
        SALES_DATE
    )
    
select
    W.WEEK
    ,ifnull(round(avg(CNT_ORDER)),0) as AVG_CNT
    ,ifnull(sum(AVG_AMT) || '円','0円') as AVG_AMT
from
    DATA_TBL as D
    right outer join
    WEEK_TBL as W
    on D.WEEK_NO=W.WEEK_NO
group by
    W.WEEK_NO
order by
    W.WEEK_NO;