with TBL1 as ( -- 日毎集計
    select
        strftime('%Y-%m-%d',SALES_DATE) as DATE 
        ,count(*) as DAY_CNT
        ,sum(SALES_AMT) as DAY_SUM
    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('%Y-%m-%d',SALES_DATE)
)
, TBL2 as (  --曜日毎集計
    select 
     strftime('%w', DATE)    as week
    , round ( avg ( DAY_CNT) ,0 ) as week_cnt --一日平均件数
    , cast( round ( avg ( DAY_SUM) ,0 ) as string ) as week_amt --一日平均金額
    from TBL1 
    group by strftime('%w', DATE)
)   
, TBL3 as (
    select 0 as week_no , '0_日'as week_name
    union select 1 , '1_月'as week_name
    union select 2 , '2_火'as week_name
    union select 3 , '3_水'as week_name
    union select 4 , '4_木'as week_name
    union select 5 , '5_金'as week_name
    union select 6 , '6_土'as week_name
)
select  substr(TBL3.week_name,3) as WEEK
    , ifnull(TBL2.week_cnt , 0) as AVG_CNT
--    , ifnull(TBL2.week_amt , 0) as AVG_AMT0
    , ifnull( 
        substr(TBL2.week_amt,-8,-3)  || case when TBL2.week_amt > '1000000' then ',' else '' end
        || substr(TBL2.week_amt,-5,-3) || case when TBL2.week_amt > '1000' then ',' else '' end
        || substr(TBL2.week_amt,-2,-3) , 0)  || '円'  as AVG_AMT
    from TBL3 left outer join TBL2 on TBL3.week_no = cast(TBL2.week as intger)
    order by TBL3.week_name