select 
    WEEK as 'WEEK'
    , (CASE WHEN cnt is null THEN 0 ELSE round(cnt /4.0) END) as 'AVG_CNT'
    ,  case
        when  length((CASE WHEN amt is null THEN 0 ELSE CAST(round(amt/4.0) AS INTEGER) END)) > 6
            then substr((CASE WHEN amt is null THEN 0 ELSE CAST(round(amt/4.0) AS INTEGER) END),1,length((CASE WHEN amt is null THEN 0 ELSE CAST(round(amt/4.0) AS INTEGER) END))-6) || ',' || substr((CASE WHEN amt is null THEN 0 ELSE CAST(round(amt/4.0) AS INTEGER) END),length((CASE WHEN amt is null THEN 0 ELSE CAST(round(amt/4.0) AS INTEGER) END))-5,3) || ',' || substr((CASE WHEN amt is null THEN 0 ELSE CAST(round(amt/4.0) AS INTEGER) END),length((CASE WHEN amt is null THEN 0 ELSE CAST(round(amt/4.0) AS INTEGER) END))-2,3)
        when  length((CASE WHEN amt is null THEN 0 ELSE CAST(round(amt/4.0) AS INTEGER) END)) > 3
            then substr((CASE WHEN amt is null THEN 0 ELSE CAST(round(amt/4.0) AS INTEGER) END),1,length((CASE WHEN amt is null THEN 0 ELSE CAST(round(amt/4.0) AS INTEGER) END))-3) || ',' || substr((CASE WHEN amt is null THEN 0 ELSE CAST(round(amt/4.0) AS INTEGER) END),length((CASE WHEN amt is null THEN 0 ELSE CAST(round(amt/4.0) AS INTEGER) END))-2,3)
        else (CASE WHEN amt is null THEN 0 ELSE CAST(round(amt/4.0) AS INTEGER) END)
        end
            || '円' as 'AVG_AMT'
from (
    select * from (select '0' as 'wid', '日' as 'WEEK') as DUAL
    UNION ALL 
    select * from (select '1' as 'wid', '月' as 'WEEK') as DUAL
    UNION ALL 
    select * from (select '2' as 'wid', '火' as 'WEEK') as DUAL
    UNION ALL 
    select * from (select '3' as 'wid', '水' as 'WEEK') as DUAL
    UNION ALL 
    select * from (select '4' as 'wid', '木' as 'WEEK') as DUAL
    UNION ALL 
    select * from (select '5' as 'wid', '金' as 'WEEK') as DUAL
    UNION ALL 
    select * from (select '6' as 'wid', '土' as 'WEEK') as DUAL
) as 'sub'
left outer join 
    (select 
        strftime('%w', SALES_DATE) as 'wid'
        ,count(*) as 'cnt'
        ,SUM(SALES_AMT) as '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 strftime('%w', SALES_DATE)
    ) as 'sums'
    on sums.wid = sub.wid
order by sub.wid
;