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,length(AVG_AMT)-5,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;