# Thread: How do I create a percentage in my SQL?

1. Registered 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

2. Registered 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```
HTH & Regards,

RBARAER

3. Registered 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?

4. Registered 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```
If (sum1 + sum2) is 0, then both are 0 (I suppose both are >= 0), so we divide by 1, else by (sum1 + sum2).

Does that work as you expect ?

Regards,

RBARAER

5. Registered 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!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•