CREATE TEMPORARY TABLE weekday_sales AS
SELECT
  strftime ('%w', SALES_DATE) AS WEEK_NO,
  COUNT(SALES_DATE) AS SUM_DATE,
  SUM(SALES_AMT) AS SUM_AMT
FROM
  SALES
WHERE
  SALES_TYPE != 2
  AND UPDATED_NO IS NULL
GROUP BY
  SALES_DATE
UNION
SELECT
  "0" AS WEEK_NO,
  0 AS AVG_CNT,
  0 AS AVG_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,
  CASE
    WHEN WEEK_NO IN ("5", "6") THEN ROUND(CAST(SUM(SUM_DATE) AS REAL) / 5)
    ELSE ROUND(CAST(SUM(SUM_DATE) AS REAL) / 4)
  END AS AVG_CNT,
  CASE
    WHEN WEEK_NO IN ("5", "6") THEN printf ("%,d", ROUND(CAST(SUM(SUM_AMT) AS REAL) / 5)) || "円"
    ELSE printf ("%,d", ROUND(CAST(SUM(SUM_AMT) AS REAL) / 4)) || "円"
  END AS AVG_AMT
FROM
  weekday_sales
GROUP BY
  week_NO
ORDER BY
  week_NO;