CODEWITHSUNDEEP
sqloracle-databasewindow-functions
dataDumpsterFire
dataDumpsterFire

Reputation: 29

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

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.

Upvotes: 1

Views: 238

Answers (2)

MT0
MT0

Reputation: 168470

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

Upvotes: 0

d r
d r

Reputation: 7846

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             */

Upvotes: 0

Related Questions