with table1 as (
    select 
        case strftime("%w", sales_date) 
            WHEN '0' THEN '日'
            WHEN '1' THEN '月'
            WHEN '2' THEN '火'
            WHEN '3' THEN '水'
            WHEN '4' THEN '木'
            WHEN '5' THEN '金'
            WHEN '6' THEN '土'
        end as WEEK,
        strftime("%w", sales_date) as week_number,
        count(*) as counts,
        sum(sales_amt) as sums
    from 
        SALES
    where
        SALES_TYPE <> 2
        and UPDATED_NO is null
        and sales_date between "2024-03-01" and "2024-03-28"
    group by
        WEEK
)

select  
    WEEK, 
    round(sum(AVG_CNT)/4.0, 0) as AVG_CNT, 
    printf("%,d", round(sum(AVG_AMT)/4.0, 0))||"円" as AVG_AMT
from (
select 
    WEEK, 
    counts as AVG_CNT, 
    sums as AVG_AMT,
    week_number
from 
    table1
) 
union select "日" as WEEK, 0 as AVG_CNT, 0 as AVG_AMT, "0" as week_number
union select "月" as WEEK, 0 as AVG_CNT, 0 as AVG_AMT, "1" as week_number
union select "火" as WEEK, 0 as AVG_CNT, 0 as AVG_AMT, "2" as week_number
union select "水" as WEEK, 0 as AVG_CNT, 0 as AVG_AMT, "3" as week_number
union select "木" as WEEK, 0 as AVG_CNT, 0 as AVG_AMT, "4" as week_number
union select "金" as WEEK, 0 as AVG_CNT, 0 as AVG_AMT, "5" as week_number
union select "土" as WEEK, 0 as AVG_CNT, 0 as AVG_AMT, "6" as week_number

group by
    week_number
order by
    week_number