Results 1 to 5 of 5
  1. #1
    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. #2
    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. #3
    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. #4
    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. #5
    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
  •