SELECT WEEK_NAME AS WEEK,
    CASE WHEN AVG_CNT IS NULL THEN 0 ELSE AVG_CNT END AS AVG_CNT,
    CASE WHEN LENGTH(SM) > 6 THEN SUBSTR(SM,-9,3) || ',' || SUBSTR(SM,-6,3) || ',' || SUBSTR(SM,-3,3) || '円'
         WHEN LENGTH(SM) > 3 THEN SUBSTR(SM,-6,3) || ',' || SUBSTR(SM,-3,3) || '円'
         WHEN SM IS NULL THEN '0円'
         ELSE SM || '円' END AS AVG_AMT
FROM (SELECT
	BASE.WEEK_NUM,
	BASE.WEEK_NAME,
	ROUND(AVG(CNT_DAY),0) AVG_CNT,
	CAST(ROUND(AVG(SUM_DAY),0) AS INT) SM,
	AVG(SUM_DAY) SUM_DAY
FROM
	(SELECT 0 AS WEEK_NUM,'日' AS WEEK_NAME UNION SELECT 1,'月' UNION SELECT 2,'火' UNION SELECT 3,'水' UNION SELECT 4,'木' UNION SELECT 5,'金' UNION SELECT 6,'土') AS BASE
	LEFT JOIN
	(SELECT 
		MOD(SUBSTR(SALES_DATE,-2)*1+4,7) AS WEEK_NUM,
		COUNT(1) CNT_DAY,
		SUM(SALES_AMT) SUM_DAY
	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) AS SUB
	ON BASE.WEEK_NUM = SUB.WEEK_NUM
GROUP BY BASE.WEEK_NAME)
ORDER BY WEEK_NUM