Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004

    Unanswered: Is there a coalesce bug when summing?

    I wrote a query that gets values and sums them. No big deal. However, my final value of "univ_reg_only_total" does not calculate correctly. I have calculated by hand all the rows and columns, and my coalesce sum is 40 counts off.

    Is there a bug in Oracle or Coalesce that could cause this? It's a pretty simple query in my opinion, but if there is a better way to do this please let me know. I've shortened it to show what I am doing.

    SELECT DISTINCT SUM(Hr_Metrics_Types.banded_staff) as univ_banded_staff, 
     SUM(Hr_Metrics_Types.barg_unit) as univ_barg_unit,
          Coalesce(Sum(banded_staff), 0)
        + Coalesce(Sum(barg_unit), 0)
        + Coalesce(Sum(acad_faculty), 0)
        + Coalesce(Sum(acad_non_faculty), 0)
        + Coalesce(Sum(acad_executive), 0)
     AS univ_reg_only_total
    FROM ....
    WHERE .....

  2. #2
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    I'd say it should rather be
    Sum(Coalesce(banded_staff, 0))...

Posting Permissions

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