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_avg_cnt
    , cast ( round ( sum ( DAY_SUM) ,0 ) as string ) as week_sum_amt
    from TBL1 
    group by strftime('%w', DATE)
)   
, TBL3 as (
    select 0 as week_no , '日'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  TBL3.week_name 
    , ifnull(TBL2.week_avg_cnt , 0) as AVG_CNT
--    , ifnull(TBL2.week_sum_amt , 0) as AVG_AMT
    , ifnull( 
        substr(TBL2.week_sum_amt,-8,-3)  || case when TBL2.week_sum_amt > '1000000' then ',' else '' end
        || substr(TBL2.week_sum_amt,-5,-3) || case when TBL2.week_sum_amt > '1000' then ',' else '' end
        || substr(TBL2.week_sum_amt,-2,-3) , 0)  || '円'  as AVG_AMT
    from TBL3 left outer join TBL2 on TBL3.week_no = cast(TBL2.week as intger)
--    order by ROWID