with TBL1 as ( -- 日毎集計
    select
        SALES_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 SALES_DATE
)
, TBL2 as (  --曜日毎集計
    select strftime('%w', SALES_DATE)    as weekno
    , round ( avg ( cast(DAY_CNT as real) ) ,0 ) as week_cnt --一日平均件数
    , cast( round ( avg ( cast(DAY_SUM as real) ) ,0 ) as string ) as week_amt --一日平均金額
    from TBL1 
    group by strftime('%w', SALES_DATE )  
)   
, TBL3 as (
    select '0_日'as week_name
    union select '1_月'as week_name
    union select '2_火'as week_name
    union select '3_水'as week_name
    union select '4_木'as week_name
    union select '5_金'as week_name
    union select '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
    ,  case when TBL2.week_amt is NULL then '0円' else
        case when TBL2.week_amt > '999999' then substr(TBL2.week_amt,-8,-3) || ',' else '' end
        || case when TBL2.week_amt > '999' then substr(TBL2.week_amt,-5,-3) || ',' else '' end
        || substr(TBL2.week_amt,-2,-3) || '円' end as AVG_AMT
    from TBL3 left outer join TBL2 on substr(TBL3.week_name,1,1) = TBL2.weekno
    order by  substr(TBL3.week_name,1,1)