-- 売上データの件数と金額合計を曜日別に集計
WITH SalesData AS (
  SELECT
    strftime('%w', SALES_DATE) AS WeekDay,
    COUNT(*) AS Cnt,
    SUM(SALES_AMT) AS TotalAmt
  FROM SALES
  WHERE SALES_DATE BETWEEN '2024-03-01' AND '2024-03-28'
    AND SALES_TYPE <> 2
    AND UPDATED_NO IS NULL
  GROUP BY strftime('%w', SALES_DATE)
),
-- 曜日テーブルを生成
WeekDays AS (
  SELECT '0' AS WeekDay UNION
  SELECT '1' UNION
  SELECT '2' UNION
  SELECT '3' UNION
  SELECT '4' UNION
  SELECT '5' UNION
  SELECT '6'
)
-- 最終的な集計と表示
SELECT
  CASE WeekDays.WeekDay
    WHEN '0' THEN '日'
    WHEN '1' THEN '月'
    WHEN '2' THEN '火'
    WHEN '3' THEN '水'
    WHEN '4' THEN '木'
    WHEN '5' THEN '金'
    WHEN '6' THEN '土'
  END AS WEEK,
  COALESCE(ROUND(AVG(SalesData.Cnt), 0), 0) AS AVG_CNT,
  printf("%,d円",COALESCE(ROUND(AVG(SalesData.TotalAmt), 0), 0)) AS AVG_AMT
FROM WeekDays
LEFT JOIN SalesData ON WeekDays.WeekDay = SalesData.WeekDay
GROUP BY WeekDays.WeekDay
ORDER BY WeekDays.WeekDay;