Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (CHECK_NUM, EMP_ID, NAME, PAY_TYPE, AMT) AS
SELECT '01', 1, 'JEFF',  'SALARY', 1500 FROM DUAL UNION ALL
SELECT '02', 2, 'BOB',   'SALARY', 2000 FROM DUAL UNION ALL
SELECT '03', 2, 'BOB',   'SALARY', 2000 FROM DUAL UNION ALL
SELECT '04', 2, 'BOB',   'BONUS',  1000 FROM DUAL UNION ALL
SELECT '05', 5, 'JAKE',  'SALARY', 1800 FROM DUAL UNION ALL
SELECT '06', 6, 'MEGAN', 'SALARY', 1900 FROM DUAL UNION ALL
SELECT '07', 7, 'AMY',   'SALARY', 2000 FROM DUAL UNION ALL
SELECT '08', 7, 'AMY',   'BONUS',  1000 FROM DUAL UNION ALL
SELECT '09', 7, 'AMY',   'SALARY', 2000 FROM DUAL UNION ALL
SELECT '10', 7, 'AMY',   'BONUS',  1000 FROM DUAL;
10 rows affected

SELECT t.*,
       CASE pay_type
       WHEN 'BONUS'
       THEN SUM(amt) OVER (PARTITION BY emp_id)
       END AS newcol
FROM   table_name t


CHECK_NUM
EMP_ID
NAME
PAY_TYPE
AMT
NEWCOL


01
1
JEFF
SALARY
1500
null

02
2
BOB
SALARY
2000
null

03
2
BOB
SALARY
2000
null

04
2
BOB
BONUS
1000
5000

05
5
JAKE
SALARY
1800
null

06
6
MEGAN
SALARY
1900
null

07
7
AMY
SALARY
2000
null

08
7
AMY
BONUS
1000
6000

09
7
AMY
SALARY
2000
null

10
7
AMY
BONUS
1000
6000


SELECT t.*,
       CASE pay_type
       WHEN 'BONUS'
       THEN SUM(amt) OVER (PARTITION BY emp_id ORDER BY check_num)
       END AS newcol
FROM   table_name t


CHECK_NUM
EMP_ID
NAME
PAY_TYPE
AMT
NEWCOL


01
1
JEFF
SALARY
1500
null

02
2
BOB
SALARY
2000
null

03
2
BOB
SALARY
2000
null

04
2
BOB
BONUS
1000
5000

05
5
JAKE
SALARY
1800
null

06
6
MEGAN
SALARY
1900
null

07
7
AMY
SALARY
2000
null

08
7
AMY
BONUS
1000
3000

09
7
AMY
SALARY
2000
null

10
7
AMY
BONUS
1000
6000


SELECT check_num,
       emp_id,
       name,
       pay_type,
       amt,
       newcol
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY check_num) AS rn
  FROM   table_name t
)
MODEL
  PARTITION BY (emp_id)
  DIMENSION BY (rn)
  MEASURES (
    check_num,
    name,
    pay_type,
    amt,
    0 AS total,
    0 AS last_bonus,
    0 AS newcol
  )  
  RULES (
    total[rn]      = amt[cv(rn)]
                     + COALESCE(total[cv(rn) - 1], 0),
    last_bonus[rn] = CASE pay_type[cv(rn)]
                     WHEN 'BONUS'
                     THEN total[cv(rn)]
                     ELSE COALESCE(last_bonus[cv(rn) - 1], 0)
                     END,
    newcol[rn]     = CASE pay_type[cv(rn)]
                     WHEN 'BONUS'
                     THEN last_bonus[cv(rn)] - COALESCE(last_bonus[cv(rn) - 1], 0)
                     END
  )


CHECK_NUM
EMP_ID
NAME
PAY_TYPE
AMT
NEWCOL


01
1
JEFF
SALARY
1500
null

06
6
MEGAN
SALARY
1900
null

02
2
BOB
SALARY
2000
null

03
2
BOB
SALARY
2000
null

04
2
BOB
BONUS
1000
5000

05
5
JAKE
SALARY
1800
null

07
7
AMY
SALARY
2000
null

08
7
AMY
BONUS
1000
3000

09
7
AMY
SALARY
2000
null

10
7
AMY
BONUS
1000
3000

CHECK_NUM	EMP_ID	NAME	PAY_TYPE	AMT	NEWCOL
01	1	JEFF	SALARY	1500	null
02	2	BOB	SALARY	2000	null
03	2	BOB	SALARY	2000	null
04	2	BOB	BONUS	1000	5000
05	5	JAKE	SALARY	1800	null
06	6	MEGAN	SALARY	1900	null
07	7	AMY	SALARY	2000	null
08	7	AMY	BONUS	1000	6000
09	7	AMY	SALARY	2000	null
10	7	AMY	BONUS	1000	6000