WITH abc as (SELECT
    CAST(STRFTIME('%w', SALES_DATE) AS INTEGER) AS DAY_OF_WEEK,
    CASE CAST(STRFTIME('%w', SALES_DATE) AS INTEGER)
        WHEN 0 THEN '日'
        WHEN 1 THEN '月'
        WHEN 2 THEN '火'
        WHEN 3 THEN '水'
        WHEN 4 THEN '木'
        WHEN 5 THEN '金'
        ELSE '土'
    END AS WEEK,
    IFNULL(ROUND(SUM(CASE WHEN SALES_TYPE <> 2 AND UPDATED_NO IS NULL THEN 1 ELSE 0 END) / 4.0, 0), 0) AS AVG_CNT,
    IFNULL(
            case
            when cast(ROUND(SUM(CASE WHEN SALES_TYPE <> 2 AND UPDATED_NO IS NULL THEN SALES_AMT ELSE 0 END) / 4.0, 0) as int) <1000 then
                cast(ROUND(SUM(CASE WHEN SALES_TYPE <> 2 AND UPDATED_NO IS NULL THEN SALES_AMT ELSE 0 END) / 4.0, 0) as int)
            when cast(ROUND(SUM(CASE WHEN SALES_TYPE <> 2 AND UPDATED_NO IS NULL THEN SALES_AMT ELSE 0 END) / 4.0, 0) as int) < 1000000 then
                cast(ROUND(SUM(CASE WHEN SALES_TYPE <> 2 AND UPDATED_NO IS NULL THEN SALES_AMT ELSE 0 END) / 4.0 , 0)/1000 as int) || ','|| cast(ROUND(SUM(CASE WHEN SALES_TYPE <> 2 AND UPDATED_NO IS NULL THEN SALES_AMT ELSE 0 END) / 4.0, 0)%1000 as int)
            when cast(ROUND(SUM(CASE WHEN SALES_TYPE <> 2 AND UPDATED_NO IS NULL THEN SALES_AMT ELSE 0 END) / 4.0, 0) as int) < 1000000000 then
                cast(ROUND(SUM(CASE WHEN SALES_TYPE <> 2 AND UPDATED_NO IS NULL THEN SALES_AMT ELSE 0 END) / 4.0 , 0)/1000000 as int)
                || ','||
                cast(ROUND(SUM(CASE WHEN SALES_TYPE <> 2 AND UPDATED_NO IS NULL THEN SALES_AMT ELSE 0 END) / 4.0 , 0)/1000 %1000 as int)
                || ','||
                cast(ROUND(SUM(CASE WHEN SALES_TYPE <> 2 AND UPDATED_NO IS NULL THEN SALES_AMT ELSE 0 END) / 4.0, 0)%1000 as int)
            END
            || '円'
        , '0円'
        ) AS AVG_AMT
FROM
    SALES
GROUP BY
    DAY_OF_WEEK
ORDER BY
    DAY_OF_WEEK
)
SELECT WEEK,AVG_CNT,AVG_AMT FROM abc;