with t as(
select '日' as 'WEEK', 0 as 'AVG_CNT', 0 as 'AVG_AMT', 0 as t
union all
select '月' as 'WEEK', 0 as 'AVG_CNT', 0 as 'AVG_AMT', 1 as t
union all
select '火' as 'WEEK', 0 as 'AVG_CNT', 0 as 'AVG_AMT', 2 as t
union all
select '水' as 'WEEK', 0 as 'AVG_CNT', 0 as 'AVG_AMT', 3 as t
union all
select '木' as 'WEEK', 0 as 'AVG_CNT', 0 as 'AVG_AMT', 4 as t
union all
select '金' as 'WEEK', 0 as 'AVG_CNT', 0 as 'AVG_AMT', 5 as t
union all
select '土' as 'WEEK', 0 as 'AVG_CNT', 0 as 'AVG_AMT', 6 as t
union all
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',
count(*) as 'AVG_CNT', sum(sales_amt) as 'AVG_AMT', strftime('%w', sales_date) as t
from sales
where sales_date between '2024-03-01' and '2024-03-28'
and sales_type!=2 and updated_no is null
group by 1
)
select WEEK, round(sum(AVG_CNT)/4.0) as 'AVG_CNT',
printf("%,d",round(sum(avg_amt)/4.0))||'円' as 'AVG_AMT' from t
group by 1
order by t
-- printf("%,d",round(sum(sales_amt)/4.0))||'円'