WITH t AS ( SELECT 'CGV' cd, 10000 amt FROM dual UNION ALL SELECT 'CGV', 10000 FROM dual UNION ALL SELECT 'b' , 1000 FROM dual UNION ALL SELECT 'B' , 5000 FROM dual --UNION ALL SELECT 'XXX', 20000 FROM dual ) SELECT LISTAGG(DECODE(rk, 1, cd), ',') WITHIN GROUP(ORDER BY cd) rank_1_cd_list , SUM(cnt) tot_cnt FROM (SELECT cd , SUM(amt) amt , COUNT(*) cnt , RANK() OVER(ORDER BY SUM(amt) DESC) rk FROM t GROUP BY cd ) ;