WITH TMP(W_NUM, WEEK) AS (
    SELECT 0, '日'
    UNION
    SELECT 1, '月'
    UNION
    SELECT 2, '火'
    UNION
    SELECT 3, '水'
    UNION
    SELECT 4, '木'
    UNION
    SELECT 5, '金'
    UNION
    SELECT 6, '土'
), TMP2(W_NUM, CNT, SUM_AMT) AS (
    SELECT 
        CAST(STRFTIME('%w',DATE(S.SALES_DATE)) AS INT),
        SUM(CASE 
                WHEN SALES_TYPE=2 OR UPDATED_NO IS NOT NULL THEN 0
                ELSE 1
            END),
        SUM(CASE 
                WHEN SALES_TYPE=2 OR UPDATED_NO IS NOT NULL THEN 0
                ELSE SALES_AMT
            END)
    FROM SALES S
    GROUP BY SALES_DATE
)

SELECT
    T.WEEK AS WEEK,
    CASE
        WHEN S.AVG_CNT IS NULL THEN 0
        ELSE S.AVG_CNT
    END AS AVG_CNT,
    CASE 
        WHEN S.AVG_AMT IS NULL THEN 0
        ELSE LTRIM(SUBSTR(S.AVG_AMT,-6,-3) 
                    || ',' || SUBSTR(S.AVG_AMT,-3,-3) 
                    || ',' || SUBSTR(S.AVG_AMT,LENGTH(S.AVG_AMT)+1,-3),',') 
                    || '円'
    END AS AVG_AMT
FROM TMP AS T
LEFT OUTER JOIN (
        SELECT
            W_NUM,
            ROUND(AVG(CNT)) AS AVG_CNT,
            CAST(ROUND(AVG(SUM_AMT)) AS INT) AS AVG_AMT
        FROM TMP2
        GROUP BY W_NUM
    ) AS S
ON T.W_NUM=S.W_NUM
GROUP BY T.W_NUM
ORDER BY T.W_NUM;