WITH T AS (
SELECT 1 ID, '01' id_card, 1 person_type, '2001-01-01' fee_date, 20 fee_amount FROM dual
UNION ALL
SELECT 2 ID, '02' id_card, 1 person_type, '2002-01-01' fee_date, 20 fee_amount FROM dual
UNION ALL
SELECT 3 ID, '01' id_card, 1 person_type, '2003-01-01' fee_date, 20 fee_amount FROM dual
UNION ALL
SELECT 4 ID, '02' id_card, 2 person_type, '2004-01-01' fee_date, 20 fee_amount FROM dual
UNION ALL
SELECT 5 ID, '03' id_card, 2 person_type, '2005-01-01' fee_date, 20 fee_amount FROM dual
UNION ALL
SELECT 6 ID, '04' id_card, 1 person_type, '2006-01-01' fee_date, 20 fee_amount FROM dual
UNION ALL
SELECT 7 ID, '01' id_card, 1 person_type, '2007-01-01' fee_date, 20 fee_amount FROM dual
UNION ALL
SELECT 8 ID, '03' id_card, 1 person_type, '2008-01-01' fee_date, 20 fee_amount FROM dual
UNION ALL
SELECT 9 ID, '01' id_card, 1 person_type, '2009-01-01' fee_date, 20 fee_amount FROM dual
UNION ALL
SELECT 10 ID, '04' id_card, 2 person_type, '2010-01-01' fee_date, 20 fee_amount FROM dual
)
SELECT PERSON_TYPE, SUM(COUNT_), SUM(COUNT_2)
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY ID_CARD ORDER BY FEE_DATE DESC) RN,
COUNT(*) OVER(PARTITION BY ID_CARD) COUNT_,
COUNT(*) OVER(PARTITION BY ID_CARD) * FEE_AMOUNT COUNT_2,
T.*
FROM T)
WHERE RN = 1
GROUP BY PERSON_TYPE;
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY ID_CARD ORDER BY FEE_DATE DESC) RN,
COUNT(*) OVER(PARTITION BY ID_CARD) COUNT_,
COUNT(*) OVER(PARTITION BY ID_CARD) * FEE_AMOUNT COUNT_2,
T.*
FROM T)
WHERE RN = 1
GROUP BY PERSON_TYPE;