우아...풀었는데.... 역시나 어렵다...ㅠ.ㅠ 모두 화이팅

SELECT grp,
max(jumsu),
count(*) CNT,
count(*) KEEP(DENSE_RANK FIRST ORDER BY JUMSU desc) F_CNT
FROM t
GROUP BY grp

잘 플어주셨네요. 정렬 안하신것 빼고요..
정답 올렸습니다. 확인해 보세요.

SELECT grp, max(jumsu) as max_jumsu, count(grp) as cnt_grp
, count(*) KEEP(DENSE_RANK FIRST ORDER BY jumsu desc) max_cnt
FROM t
group by grp
order by max_cnt desc
;; keep 에서 삽질...

keep이란것이 있군요?

저도 keep 처음 알았네요. 유용하네요. max() keep(), min() keep(), count() keep().

WITH t AS
(
SELECT 1 grp, 99 jumsu FROM dual
UNION ALL SELECT 1, 99 FROM dual
UNION ALL SELECT 1, 98 FROM dual
UNION ALL SELECT 1, 97 FROM dual
UNION ALL SELECT 2, 87 FROM dual
UNION ALL SELECT 2, 87 FROM dual
UNION ALL SELECT 2, 87 FROM dual
UNION ALL SELECT 2, 87 FROM dual
UNION ALL SELECT 2, 86 FROM dual
UNION ALL SELECT 3, 76 FROM dual
UNION ALL SELECT 3, 69 FROM dual
UNION ALL SELECT 3, 59 FROM dual
)
SELECT T2.GRP, T2.TOT_CNT, T2.MAX_JUMSU, T1.MAX_CNT
FROM (SELECT GRP, JUMSU, COUNT(GRP) AS MAX_CNT FROM T GROUP BY GRP, JUMSU ) T1,
(SELECT GRP, COUNT(GRP)AS TOT_CNT, MAX(JUMSU) AS MAX_JUMSU FROM T GROUP BY GRP) T2
WHERE T1.GRP = T2.GRP
AND T1.JUMSU = T2.MAX_JUMSU
ORDER BY MAX_CNT DESC

젠장 공부 열심히 하겠습니다;;;

select distinct grp, max_jumsu, tot_cnt , count(jumsu)over(partition by grp) max_cnt
from(
SELECT distinct max(jumsu)over(partition by grp) max_jumsu ,
count(jumsu)over(partition by grp) tot_cnt
FROM t
) a ,t
where max_Jumsu = jumsu

SELECT grp, MAX(jumsu) MAX_JUMSU, count(jumsu) TOT_CNT, COUNT(DECODE(rk,'1',1)) MAX_CNT
FROM(
  SELECT grp,jumsu, rank() OVER(PARTITION BY grp ORDER BY jumsu DESC) rk
  FROM t
)
GROUP BY grp

SELECT grp, MAX(jumsu) MAX_JUMSU, count(jumsu) TOT_CNT, COUNT(DECODE(rk,'1',1)) MAX_CNT
FROM(
  SELECT grp,jumsu, rank() OVER(PARTITION BY grp ORDER BY jumsu DESC) rk
  FROM t
)
GROUP BY grp

위에꺼 제가 올렸습니다; 로그인안하고 올렸네요;