Code:
SELECT
B.SERVICE_TYPE || '|' ||
B.TOTAL_TYPE || '|' ||
B.CALL_INDICATOR || '|' ||
B.A_NUMBER || '|' ||
B.APN || '|' ||
B.DAY || '|' ||
B.HOUR || '|' ||
B.DURATION || '|' ||
B.PARTIAL_INDICATOR || '|' ||
B.EQUIPMENT_ID_A || '|' ||
B.LOCATION_AREA_ID || '|' ||
B.DATA_VOLUME_INCOMING|| '|' ||
B.DATA_VOLUME_OUTGOING|| '|' ||
B.DW_BATCH_ID || '|' ||
B.CALL
FROM (
SELECT
ROW_NUMBER()
OVER (PARTITION BY SERVICE_TYPE,TOTAL_TYPE,CALL_INDICATOR,A_NUMBER,APN,DAY,HOUR
ORDER BY SERVICE_TYPE,TOTAL_TYPE,CALL_INDICATOR,A_NUMBER,APN,DAY,HOUR) RN
,SERVICE_TYPE
,TOTAL_TYPE
,CALL_INDICATOR
,A_NUMBER
,APN
,DAY
,HOUR
,PARTIAL_INDICATOR
,EQUIPMENT_ID_A
,LOCATION_AREA_ID
,DW_BATCH_ID
,SUM(DURATION)
OVER (PARTITION BY SERVICE_TYPE,TOTAL_TYPE,CALL_INDICATOR,A_NUMBER,APN,DAY,HOUR
ORDER BY SERVICE_TYPE,TOTAL_TYPE,CALL_INDICATOR,A_NUMBER,APN,DAY,HOUR) DURATION
,SUM(DATA_VOLUME_INCOMING)
OVER (PARTITION BY SERVICE_TYPE,TOTAL_TYPE,CALL_INDICATOR,A_NUMBER,APN,DAY,HOUR
ORDER BY SERVICE_TYPE,TOTAL_TYPE,CALL_INDICATOR,A_NUMBER,APN,DAY,HOUR) DATA_VOLUME_INCOMING
,SUM(DATA_VOLUME_OUTGOING)
OVER (PARTITION BY SERVICE_TYPE,TOTAL_TYPE,CALL_INDICATOR,A_NUMBER,APN,DAY,HOUR
ORDER BY SERVICE_TYPE,TOTAL_TYPE,CALL_INDICATOR,A_NUMBER,APN,DAY,HOUR) DATA_VOLUME_OUTGOING
,SUM(CALL)
OVER (PARTITION BY SERVICE_TYPE,TOTAL_TYPE,CALL_INDICATOR,A_NUMBER,APN,DAY,HOUR
ORDER BY SERVICE_TYPE,TOTAL_TYPE,CALL_INDICATOR,A_NUMBER,APN,DAY,HOUR) CALL
FROM
P_NETWORK_GPRS_1) B
WHERE B.RN=1;
