Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    83

    Unanswered: How to sum columns and rows

    I have a query where I sum the results, and then sum those results by row. What I'd also like to do is sum the column also. I thought about just creating a separate query, but I'd like to have it all in one query.

    Here is my query:

    SELECT DISTINCT a.unitid, SUM(a.banded_staff) as banded_staff, SUM(a.barg_unit) as barg_unit, Coalesce(Sum(banded_staff), 0) + Coalesce(Sum(barg_unit), 0) AS unit_total
    FROM myTable
    GROUP BY unitid
    ORDER BY unitid

    And here are my results :
    UNITID BANDED_STAFF BARG_UNIT UNIT_TOTAL
    Unit1 10 11 21
    Unit2 4 3 7

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    It is not necesary to use DISTINCT with GROUP BY, to sum columns do this:
    Code:
    COMP SUM OF BANDED_STAFF, BARG_UNIT, UNIT_TOTAL ON REPORT
    BREAK ON REPORT
    SELECT ...FROM...GROUP BY...ORDER BY...;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jan 2004
    Posts
    83
    How does comp sum work? I couldn't get it to work within my query, and when I did a google search for "comp sum" I came up with virtually nothing.

    I should also mention that I am doing my query within Cold Fusion and not directly in Oracle...I'm hitting an Oracle database.

  4. #4
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    COMP is COMPUTE... and you won't be able to use it inside Cold Fusion.

    What about this?

    Code:
    SELECT a.unitid, SUM(a.banded_staff) as banded_staff, SUM(a.barg_unit) as barg_unit, Coalesce(Sum(banded_staff), 0) + Coalesce(Sum(barg_unit), 0) AS unit_total
    FROM myTable
    GROUP BY unitid
    UNION
    SELECT null as unitid, SUM(a.banded_staff) as banded_staff, SUM(a.barg_unit) as barg_unit, Coalesce(Sum(banded_staff), 0) + Coalesce(Sum(barg_unit), 0) AS unit_total
    FROM myTable
    ORDER BY unitid

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    But if you sum the column also, you will loose the group by .. or if you want to run it on all of the a.unitid ( and having the result on each, therefore ), you can run sum( banded_staff ) over( ) .. on a view of your parent query.

Posting Permissions

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