Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2007
    Posts
    29

    Unanswered: Summation Of Varying Columns

    Hi all,
    I am running an oracle 10g database.
    I am selecting values from different columns of the database and summing them together to produce a new column entirely. The summation involves manipulating different no. of columns based on the month of the year i am dealing with. I start with month 12 and sum all the 12 column values, when i get to 11, the columns reduce again until i finally get to one which leaves me with only one column value (see below). I feel there should be a simpler way of doing it than going through the routine i have below, reducing all the column values as the month changes.
    Please can someone advice on best solution ASAP.
    Thank you



    SELECT
    '12' PERIOD,
    ACTCURY01+ACTCURY02+ACTCURY03+ACTCURY04+ACTCURY05+ ACTCURY06+ACTCURY07+ACTCURY08+ACTCURY09+ACTCURY10+ ACTCURY11 BF_OPENNING_BAL_SOURCE,
    AVGCUR01+AVGCUR02+AVGCUR03+AVGCUR04+AVGCUR05+AVGCU R06+AVGCUR07+AVGCUR08+AVGCUR09+AVGCUR10+AVGCUR11 BF_OPENNING_BAL_FUNCTIONAL,
    ACTCURY01+ACTCURY02+ACTCURY03+ACTCURY04+ACTCURY05+ ACTCURY06+ACTCURY07+ACTCURY08+ACTCURY09+ACTCURY10+ ACTCURY11+ACTCURY12 CF_CLOSING_BAL_SOURCE,
    AVGCUR01+AVGCUR02+AVGCUR03+AVGCUR04+AVGCUR05+AVGCU R06+AVGCUR07+AVGCUR08+AVGCUR09+AVGCUR10+AVGCUR11+A VGCUR12 CF_CLOSING_BAL_FUNCTIONAL


    FROM
    MAIN

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'd say that your main problem is a bad design. Why did you choose to store January's data into one column, February into another etc.? Instead of
    Code:
    CREATE TABLE main
    (actcury01 number,
     actcury02 number,
     actcury03 number, 
     ...
     actcury12 number
    );
    it should have been something like
    Code:
    CREATE TABLE main
    (date_column date,
     actcury number
    );
    Then it would be easy to calculate averages, sum values over months, etd. Having it as it is, I'm afraid there's nothing much you can do.

  3. #3
    Join Date
    Nov 2007
    Posts
    29

    summation

    Hi
    The tables were originally a mainframe flat file structured one. It was later migrated to oracle and since the mainframe structure was not an RDBMS structure. The columns were migrated that way because it was like an array kind of thing in the mainframe environment
    so wahts the solution

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Solution is a query from your first post.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I forgot to mention ... if you have to write more than one (this particular one) query using the same data set, perhaps you might want to create a VIEW and, in the future, use this "nicely formatted" view as a data source.

Posting Permissions

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