WITH WEEK AS (
  SELECT '0' AS NO, '日' AS NAME UNION
  SELECT '1', '月' UNION
  SELECT '2', '火' UNION
  SELECT '3', '水' UNION
  SELECT '4', '木' UNION
  SELECT '5', '金' UNION
  SELECT '6', '土'
)
SELECT
 W.NAME AS WEEK
,IFNULL(S.AVG_CNT, 0) AS AVG_CNT
-- 3桁ごとにカンマを入れ、最後に'円'を付ける。最大9桁
,REPLACE(IFNULL(CASE
     WHEN S.AVG_AMT < 1000 THEN S.AVG_AMT
     WHEN S.AVG_AMT < 10000 THEN SUBSTR(S.AVG_AMT,1,1) || ',' || SUBSTR(S.AVG_AMT,2)
     WHEN S.AVG_AMT < 100000 THEN SUBSTR(S.AVG_AMT,1,2) || ',' || SUBSTR(S.AVG_AMT,3)
     WHEN S.AVG_AMT < 1000000 THEN SUBSTR(S.AVG_AMT,1,3) || ',' || SUBSTR(S.AVG_AMT,4)
     WHEN S.AVG_AMT < 10000000 THEN SUBSTR(S.AVG_AMT,1,1) || ',' || SUBSTR(S.AVG_AMT,2,3) || ',' || SUBSTR(S.AVG_AMT,5)
     WHEN S.AVG_AMT < 100000000 THEN SUBSTR(S.AVG_AMT,1,2) || ',' || SUBSTR(S.AVG_AMT,3,3) || ',' || SUBSTR(S.AVG_AMT,6)
     ELSE SUBSTR(S.AVG_AMT,1,3) || ',' || SUBSTR(S.AVG_AMT,4,3) || ',' ||  SUBSTR(S.AVG_AMT,7)
 END, 0), '.0', '') || '円' AS AVG_AMT
FROM WEEK AS W
LEFT OUTER JOIN (
SELECT
 STRFTIME('%w', SALES_DATE) AS WEEK
-- 売上データの件数の1日平均件数を曜日別に集計(小数点以下は四捨五入) 
,ROUND(AVG(COUNT)) AS AVG_CNT
-- 売上金額合計の1日平均金額を曜日別に集計(小数点以下は四捨五入)
,ROUND(AVG(SALES_AMT)) AS AVG_AMT
FROM (
SELECT
 SALES_DATE
,COUNT(SALES_NO) AS COUNT
,SUM(SALES_AMT) AS SALES_AMT
FROM SALES
-- 2024年3月1日から28日までの4週間分
WHERE SALES_DATE BETWEEN '2024-03-01' AND '2024-03-28'
-- 集計対象外: 売上区分(SALES_TYPE) = 2 (売上返品)
AND SALES_TYPE <> 2
-- 集計対象外: 赤黒伝票番号(UPDATED_NO)の値がNULL値以外(赤伝)
AND UPDATED_NO IS NULL
GROUP BY
 SALES_DATE
) AS D
GROUP BY
 WEEK
) AS S
ON W.NO = S.WEEK
GROUP BY W.NO
ORDER BY
 W.NO