CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Daniel
Daniel

Reputation: 21

SUM two rows in Oracle

I want to SUM two amounts but I get two different rows. The amount should give me a total of "2231" and one row. They also belong to a Unique Key called DETA_AJUN_NRO.

  MONTO NAC CEDULA  CODIGO SUBCODIGO FISCAL FECHA
------- --- ------- ------ --------- ------ --------
1673,25 V   8018573 28     50        2017   11/05/17
 557,75 V   8018573 28     50        2017   30/05/17

This is the query I'm using:

SELECT DISTINCT SUM(DETA_MTO_CUOTA) MONTO,
       DETA_NACIONALIDAD NAC, DETA_CEDULA CEDULA, DETA_CODIGO CODIGO, 
       DETA_SUB_CODIGO SUBCODIGO, DETA_AJUN_ANO_FISCAL FISCAL, DETA_FEC_CRE FECHA
FROM   SRH_DETALLE_AJUS
WHERE  DETA_AJUN_NRO = 6351
AND    DETA_TIPO_REG = 'A'
AND    DETA_SUB_CODIGO = 50
GROUP BY DETA_NACIONALIDAD,DETA_CEDULA,DETA_CODIGO,DETA_SUB_CODIGO,DETA_AJUN_ANO_FISCAL,DETA_FEC_CRE

Despite using DISTINCT and GROUP BY I can't manage to SUM both rows into one.

Thank you very much.

Upvotes: 0

Views: 3131

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

You need to remove the date from the group by:

SELECT SUM(DETA_MTO_CUOTA) MONTO, DETA_NACIONALIDAD as NAC,
       DETA_CEDULA as CEDULA, DETA_CODIGO as CODIGO, 
       DETA_SUB_CODIGO as SUBCODIGO, DETA_AJUN_ANO_FISCAL as FISCAL
FROM SRH_DETALLE_AJUS
WHERE DETA_AJUN_NRO = 6351 AND
      DETA_TIPO_REG = 'A' AND
      DETA_SUB_CODIGO = 50
GROUP BY DETA_NACIONALIDAD, DETA_CEDULA, DETA_CODIGO, DETA_SUB_CODIGO, DETA_AJUN_ANO_FISCAL

Upvotes: 1

Related Questions