CREATE TEMPORARY TABLE WEEKDAY_SALES AS
SELECT
  strftime ('%w', SALES_DATE) AS WEEK_NO,
  COUNT(SALES_NO) AS SUM_DATE,
  SUM(SALES_AMT) AS SUM_AMT
FROM
  SALES
WHERE
  SALES_TYPE != 2
  AND UPDATED_NO IS NULL
  AND SALES_DATE BETWEEN '2024-03-01' AND '2024-03-28'
GROUP BY
  SALES_DATE
UNION
SELECT
  "0" AS WEEK_NO,
  0 AS SUM_DATE,
  0 AS SUM_AMT
UNION
SELECT
  "1" AS WEEK_NO,
  0 AS SUM_DATE,
  0 AS SUM_AMT
UNION
SELECT
  "2" AS WEEK_NO,
  0 AS SUM_DATE,
  0 AS SUM_AMT
UNION
SELECT
  "3" AS WEEK_NO,
  0 AS SUM_DATE,
  0 AS SUM_AMT
UNION
SELECT
  "4" AS WEEK_NO,
  0 AS SUM_DATE,
  0 AS SUM_AMT
UNION
SELECT
  "5" AS WEEK_NO,
  0 AS SUM_DATE,
  0 AS SUM_AMT
UNION
SELECT
  "6" AS WEEK_NO,
  0 AS SUM_DATE,
  0 AS SUM_AMT;

SELECT
  CASE WEEK_NO
    WHEN "0" THEN "日"
    WHEN "1" THEN "月"
    WHEN "2" THEN "火"
    WHEN "3" THEN "水"
    WHEN "4" THEN "木"
    WHEN "5" THEN "金"
    WHEN "6" THEN "土"
    WHEN "7" THEN "日"
    ELSE "不明"
  END AS WEEK,
  IFNULL (ROUND(CAST(SUM(SUM_DATE) AS REAL) / 4), 0) AS AVG_CNT,
  IFNULL (printf ("%,d", CAST(ROUND(CAST(SUM(SUM_AMT) AS REAL) / 4) AS INT)), 0) || "円" AS AVG_AMT
FROM
  WEEKDAY_SALES
GROUP BY
  WEEK_NO
ORDER BY
  WEEK_NO;