Results 1 to 5 of 5

020305, 10:30 #1Registered User
 Join Date
 Jan 2004
 Posts
 83
Unanswered: How do I create a percentage in my SQL?
I need to create a percentage in my SQL. I have summed the employee counts for each department, then use 'coalesce' to get a grand total.
I need to be able to divide the departments into the dept_total so I can display a percentage for each via SQL.
SELECT DISTINCT SUM(dept_1) as dept_1, SUM(dept_2) as dept_2,
Coalesce(Sum(dept_1), 0)
+ Coalesce(Sum(dept_2), 0) AS dept_total

020305, 12:46 #2Registered User
 Join Date
 Aug 2004
 Location
 France
 Posts
 754
Do you mean that ?
Code:SELECT DISTINCT SUM(dept_1) as dept_1, SUM(dept_2) as dept_2, NVL(Sum(dept_1), 0) + NVL(Sum(dept_2), 0) AS dept_total, NVL(Sum(dept_1), 0) * 100 / (NVL(Sum(dept_1), 0) + NVL(Sum(dept_2), 0)) as pct_dept1, NVL(Sum(dept_2), 0) * 100 / (NVL(Sum(dept_1), 0) + NVL(Sum(dept_2), 0)) as pct_dept2
RBARAER

020305, 13:30 #3Registered User
 Join Date
 Jan 2004
 Posts
 83
yes, that works...occasionally, though, I will get a "divisor is equal to zero" error. how do I catch for that?

020305, 13:49 #4Registered User
 Join Date
 Aug 2004
 Location
 France
 Posts
 754
Sorry for that . That should be better :
Code:SELECT DISTINCT SUM(dept_1) as dept_1, SUM(dept_2) as dept_2, NVL(Sum(dept_1), 0) + NVL(Sum(dept_2), 0) AS dept_total, NVL(Sum(dept_1), 0) * 100 / DECODE((NVL(Sum(dept_1), 0) + NVL(Sum(dept_2), 0)), 0, 1, (NVL(Sum(dept_1), 0) + NVL(Sum(dept_2), 0))) as pct_dept1, NVL(Sum(dept_2), 0) * 100 / DECODE((NVL(Sum(dept_1), 0) + NVL(Sum(dept_2), 0)), 0, 1, (NVL(Sum(dept_1), 0) + NVL(Sum(dept_2), 0))) as pct_dept2
Does that work as you expect ?
Regards,
RBARAER

020305, 13:56 #5Registered User
 Join Date
 Jan 2004
 Posts
 83
yes, that worked. thank you very much. i was trying to work with decode before you replied, but i had difficulty. and thanks for the explanation. i'm not a novice, but i have enough knowledge to be dangerous!