Firebird 3.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select rdb$get_context('SYSTEM', 'ENGINE_VERSION') as version
     , rdb$character_set_name
from rdb$database;


VERSION
RDB$CHARACTER_SET_NAME


3.0.10
UTF8


create table LOCATION_DETAIL (
  ID integer constraint pk_location_detail primary key,
  ITEMNO char(3),
  LOCATIONNO char(4),
  LOTNO char(3),
  INCOME integer,
  OUTGO integer
);
execute block as
begin
  insert into LOCATION_DETAIL values (1, 'AAA', 'A-01', '001', 1, 0);
  insert into LOCATION_DETAIL values (2, 'AAA', 'A-02', '002', 1, 0);
  insert into LOCATION_DETAIL values (3, 'AAA', 'A-01', '001', 0, 1);
end
3 rows affected

SELECT ITEMNO, LOCATIONNO, LOTNO, SUM(INCOME-OUTGO) STOCK
FROM LOCATION_DETAIL 
WHERE ITEMNO = 'AAA'
GROUP BY ITEMNO, LOCATIONNO, LOTNO
ORDER BY LOTNO


ITEMNO
LOCATIONNO
LOTNO
STOCK


AAA
A-01
001
0

AAA
A-02
002
1


select ITEMNO, LOCATIONNO, LOTNO, sum(INCOME-OUTGO) as STOCK
from LOCATION_DETAIL
where ITEMNO = 'AAA'
group by ITEMNO, LOCATIONNO, LOTNO
having sum(INCOME-OUTGO) > 0
order by LOTNO
fetch first row only


ITEMNO
LOCATIONNO
LOTNO
STOCK


AAA
A-02
002
1