Calculate total only for employee's having both salary and bonus

Question

I have a table which I need to make a calculated field with the total compensation for employees who have count(distinct(pay_type)) > 1. When count(distinct(pay_type)) > 1 is true I want the total amount in a column next to the amount column. My approach was to use a window function along with some kind of CASE statement, but can't figure it out.

My current query is:

Select check_num, emp_id, name, pay_type, sum(amount) as amt
from employee_paystubs
group by check_num, emp_id, name, pay_type
order by check_num desc;

This is the resulting data:

CHECK_NUM	EMP_ID	NAME	PAY_TYPE	AMT
01	1	JEFF	SALARY	1500
02	2	BOB	SALARY	2000
03	2	BOB	SALARY	2000
04	2	BOB	BONUS	1000
05	5	JAKE	SALARY	1800
06	6	MEGAN	SALARY	1900

This is my desired output:

CHECK_NUM	EMP_ID	NAME	PAY_TYPE	AMT	NEW_COL
01	1	JEFF	SALARY	1500
02	2	BOB	SALARY	2000
03	2	BOB	SALARY	2000
04	2	BOB	BONUS	1000	5000
05	5	JAKE	SALARY	1800
06	6	MEGAN	SALARY	1900

Thank you.

PS I am using Oracle.

Answer 1

One option is to use Case expression that checks for number of distinct PAY_TYPES (per EMP_ID) - and if it is > 1 (haave both: SALARY and BONUS) - fetches the Sum(AMT) in BONUS row....

WITH    --  S a m p l e    D a t a :
    tbl (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 
        )

--      S Q L :
Select    t.*,
          Case When Count(Distinct PAY_TYPE) Over(Partition By EMP_ID) > 1 And PAY_TYPE = 'BONUS'
               Then Sum(AMT) Over(Partition By EMP_ID) 
          End "NEW_COL"
From        tbl t
Order By  CHECK_NUM

/*     R e s u l t :
CHECK_NUM     EMP_ID NAME  PAY_TYPE        AMT    NEW_COL
--------- ---------- ----- -------- ---------- ----------
01                 1 JEFF  SALARY         1500           
02                 2 BOB   SALARY         2000           
03                 2 BOB   SALARY         2000           
04                 2 BOB   BONUS          1000       5000
05                 5 JAKE  SALARY         1800           
06                 6 MEGAN SALARY         1900             */

Answer 2

Depending on how you want to handle multiple BONUS lines for an employee, if you have the sample data:

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;

If you want to output the total amount for all bonus lines then:

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

Outputs:

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

If you want to output the cumulative total then:

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

Outputs:

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

If you want to restart counting from 0 after each bonus then:

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
  )

Which outputs:

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

Note: You can add a check for COUNT(DISTINCT pay_type) OVER (PARTITION BY emp_id) > 1 to the CASE expression if you want but, for your sample data, if there is a BONUS row then there always appears to be a SALARY row so it appears to be sufficient to just look for a BONUS row rather than counting all the pay types.

fiddle