I need to find the percentage of an employee’s salary that is paid by each account.
For example.. I need to find a way to sum the amounts of each employee and then divide each amount by that total.
employee 1 acct 111 $1000 employee 1 acct 112 $2000 employee 2 acct 111 $1000 employee 2 acct 112 $2000 employee 2 acct 113 $3000
I have read up about the ratio to report function in sql but it does not solve my problem as it sums every employee’s salary and then gives a % based upon that total.
I also tried to do a separate subquery where I do the simple math (x/(x+y)*100) but I don’t want to have to Group By the amount, as that increases the line output dramatically.
SELECT DISTINCT a.EMPLID, a.fund_grp_cd, a.position_nbr, a.account_nbr,
a.account_nm, a.sub_acct_nbr, a.sub_acct_nm, a.org_cd, a.org_nm,
a.fin_hgh_ed_func_cd, a.fin_object_cd, SUM(a.total)
FROM
(SELECT ld.emplid, sf.fund_grp_cd, ld.position_nbr, cat.org_cd, o.org_nm,
cat.fin_hgh_ed_func_cd, ld.account_nbr, cat.account_nm,
ld.sub_acct_nbr, s.sub_acct_nm, ld.fin_object_cd,
CASE WHEN ld.TRN_DEBIT_CRDT_CD = 'C' THEN ld.TRN_LDGR_ENTR_AMT * -1 ELSE
ld.TRN_LDGR_ENTR_AMT END AS TOTAL
FROM LD_LDGR_ENTR_T ld
INNER JOIN CA_ACCOUNT_T cat
ON ld.account_nbr = cat.account_nbr
and ld.fin_coa_cd = cat.fin_coa_cd
LEFT JOIN CA_SUB_ACCT_T s
ON s.account_nbr = ld.account_nbr
and s.sub_acct_nbr = ld.sub_acct_nbr
LEFT JOIN CA_ORG_T o
ON o.org_cd = cat.org_cd
LEFT JOIN CA_SUB_FUND_GRP_T sf
ON sf.SUB_FUND_GRP_CD = cat.SUB_FUND_GRP_CD
LEFT JOIN CA_OBJECT_CODE_T o
ON o.fin_object_cd = ld.fin_object_cd
AND o.univ_fiscal_yr = ld.univ_fiscal_yr
WHERE ld.univ_fiscal_yr = '2019'
and cat.org_cd IN
('1901','1902','1903','1904','1905','1906','1907','1908','PHRM','RX09',
'RXEE'
,'RXO1','RXO2','RXO3','RXO4','RXO5','RXO6','RXO7','RXO8','RXPX',
'RXR1','RXR2','RXR3','RXR4','RXR5','RXR6','RXR7','RXR8')
AND ld.univ_fiscal_prd_cd IN
('01','02','03','04','05','06','07','08','09','10','11','12')
AND o.rpts_to_fin_obj_cd = '1100'
AND cat.acct_closed_ind = 'N'
)a
GROUP BY a.EMPLID, a.account_nbr, a.fin_object_cd, a.account_nm,
a.sub_aenter code here`cct_nbr, a.sub_acct_nm,
a.position_nbr, a.org_cd, a.org_nm, a.fin_hgh_ed_func_cd, a.fund_grp_cd
HAVING SUM(a.total) <> 0
I would simply like to have a % of total salary for each employee/account combination. Thanks!
Advertisement
Answer
From what I understand, you are probably looking for the analytic version of ratio_to_report(), which is otherwise, indeed, the correct solution. Something like this (note that I include the test data in a WITH clause at the top; that is not part of the query, it should be removed when you apply the solution to your real-life data).
with
test_data (employee, acct, amount) as (
select 1, 111, 1000 from dual union all
select 1, 112, 2000 from dual union all
select 2, 111, 1000 from dual union all
select 2, 112, 2000 from dual union all
select 2, 113, 3000 from dual
)
select employee, acct, amount,
ratio_to_report(amount) over (partition by employee) as pct_of_pay
from test_data
;
EMPLOYEE ACCT AMOUNT PCT_OF_PAY
---------- ---------- ---------- ----------
1 112 2000 .666666667
1 111 1000 .333333333
2 112 2000 .333333333
2 113 3000 .5
2 111 1000 .166666667
(As you can see, I did not order the output; you can, if you need to.)
Notice the analytic clause, over (partition by employee); I believe that’s the part you were missing.
I don’t know how that fits with the rest of your code, where you do a lot of things that don’t seem related to the question you asked.
Also, I hope your amounts are numbers, not strings. (Not sure about the dollar symbol in front of each amount…)
You didn’t state how you want the percentages to be formatted. Without any formatting, you will get something like 0.3333333333 for 33 1/3 % (one-third). You may need to multiply by 100, cut the decimals off at two or three digits, add a percent sign, etc. – all of that can be done in the SELECT clause of the main query. However, that should only be done if this is the final output that goes straight to a human readable report; if the values are used in further processing (computations), it is best to leave them exactly as they are.