with tmp1 as (
    select SALES_DATE,
    COUNT(SALES_NO) CNT,
    SUM(SALES_AMT) AMT
    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
    ),
    tmp2 as(SELECT 
     strftime("%w", SALES_DATE) +1 as WEEKD,
     round(AVG(CNT),0) AVG_CNT,
     cast(cast( round(AVG(AMT),0) as integer) as text) AVG_AMT
    FROM tmp1
    group by 1
    ),tmp3 as (select 1 nn union select 2 nn 
    union select 3 nn union 
    select 4 nn union select 5 nn union select 6 nn union select 7 nn)
     
    SELECT
    substr('日月火水木金土',nn,1) WEEK,
    CASE WHEN 
    AVG_AMT is null then '0円'
    WHEN length(AVG_AMT)>6 then 
    substr(AVG_AMT,1,length(AVG_AMT)-6) || ',' || substr(AVG_AMT,1,length(AVG_AMT)-3) || ',' || substr(AVG_AMT,length(AVG_AMT)-2,3)||'円'
    WHEN length(AVG_AMT)>3 then 
    substr(AVG_AMT,1,length(AVG_AMT)-3) || ',' || substr(AVG_AMT,length(AVG_AMT)-2,3)||'円'
    ELSE
    AVG_AMT || '円'
     END AVG_AMT,
    CASE WHEN
    AVG_CNT is null then '0'
    ELSE AVG_CNT END AVG_CNT
    FROM 
    tmp3 left join tmp2 on tmp3.nn=tmp2.weekd
    order by nn;