CODEWITHSUNDEEP
sqloracle-database
grayfield
grayfield

Reputation: 129

Oracle - How to conditional SUM two rows

I have records like this:

|JOURNAL_NO|LINE_NO|AMOUNT|ACC_CODE|
|00001     |      1|  1800|562300C |
|00001     |      2|   900|562300C |
|00001     |      3|   450|562300C |
|00001     |   1001|   200|123222C |
|00001     |   1002|   100|123222C |
|00001     |   1003|    50|123222C |
|00002     |      1|  2700|562100C |
|00002     |      2|  4500|562100C |
|00002     |   1001|   300|123222C |
|00002     |   1002|   500|123222C |

How to SUM the AMOUNT, where LINE_NO 1 sum with LINE_NO 1001, LINE_NO 2 sum with LINE_NO 1002, etc.

This is the result that I want to achieve.

|JOURNAL_NO|LINE_NO|AMOUNT|ACC_CODE|
|00001     |      1|  2000|562300C |
|00001     |      2|  1000|562300C |
|00001     |      3|   500|562300C |
|00002     |      1|  3000|562100C |
|00002     |      2|  5000|562100C |

Upvotes: 0

Views: 251

Answers (1)

jarlh
jarlh

Reputation: 44696

Have a derived table where you use a case expression to adjust LINE_NO's > 1000.

select JOURNAL_NO, LINE_NO, SUM(AMOUNT), MAX(ACC_CODE)
from
(
select JOURNAL_NO,
       case when LINE_NO > 1000 then LINE_NO - 1000 else LINE_NO end as LINE_NO,
       AMOUNT, ACC_CODE
from tablename
) dt
group by JOURNAL_NO, LINE_NO

Upvotes: 2

Related Questions