By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table generalledger_mgmt (
rowsortid varchar(25),
COMPANYID varchar(5),
serial int,
TRANSDATEID varchar(25),
AMOUNTLC int,
CURRENCYCODE varchar(5)
);
insert into generalledger_mgmt values
('20211109-ICJ-565','SSE',222,'20211101', 500,'SEK'),
('20211109-ICJ-564','SSE',222,'20211105',-500,'SEK'),
('20211109-ICT-562','SSE',202,'20211001', 600,'SEK'),
('20211109-ICK-568','SSE',210,'20211201', 200,'SEK');
SELECT
MAX(rowsortid)
,COMPANYID
,MAX(REGEXP_SUBSTR(serial, '[^| ]+', 1, 1)) as serial
,max(TRANSDATEID)
,sum(AMOUNTLC)
,max(CURRENCYCODE)
FROM generalledger_mgmt
WHERE 1 = 1
AND companyid = 'SSE'
AND transdateid >= 20211101
AND transdateid < 20220101
GROUP BY
COMPANYID
ORDER BY MAX(REGEXP_SUBSTR
(serial, '[^| ]+', 1, 1));
| MAX(rowsortid) | COMPANYID | serial | max(TRANSDATEID) | sum(AMOUNTLC) | max(CURRENCYCODE) |
|---|---|---|---|---|---|
| 20211109-ICK-568 | SSE | 222 | 20211201 | 200 | SEK |