SELECT
TEMP.A AS "Artist Code",
TEMP.B AS "Artwork No.",
TEMP.C AS "Artwork Title", 
TEMP.D AS "Number of Movements"
FROM(
SELECT
ARTIST.artist_code          AS "A",       
ARTWORK.artwork_no          AS "B",       
ARTWORK.artwork_title       AS "C",     
COUNT(AW_STATUS.aws_action) AS "D",
AVG(COUNT(AW_STATUS.aws_action)) OVER () AS "E"
FROM ARTIST
    JOIN ARTWORK
    ON ARTIST.artist_code = ARTWORK.artist_code
    JOIN AW_STATUS
    ON AW_STATUS.artist_code = ARTWORK.artist_code
       AND AW_STATUS.artwork_no = ARTWORK.artwork_no
GROUP BY
    ARTIST.artist_code,
    ARTWORK.artwork_no,
    ARTWORK.artwork_title
) TEMP
WHERE TEMP.D < TEMP.E
ORDER BY 
    TEMP.D ASC,
    TEMP.A ASC,
    TEMP.B ASC;

以上未測

參考這篇
跟你的情形很類似

如果我沒讀錯，意思是
COUNT 是 COUNT GROUP BY 之後的結果
後面的 AVG(COUNT) 是無效的寫法
把後面的 AVG(COUNT) 改寫掉就會過了