Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    21

    Unanswered: SQLPLUS COMPUTE SUM: Is conditional summing or other manipulation possible?

    I'm developing a reconciliation report in SQLPLUS which needs summation to happen at several levels. Unfortunately, to avoid spurious variances and a cluttered appearance, I need to produce the lowest level of summation by means of a second pass through the data, rather than using COMPUTE.

    Therefore when I try to use COMPUTE at the higher levels, the amounts for that column are doubled, because they're there from the inner SELECT, and then they're there again for the lowest level totals.

    Is there any way to manipulate summation when using COMPUTE SUM? Ideally, I want to have a way that I can tell it to compute, but then divide the result by 2.
    Last edited by Pithecanthropus; 05-06-04 at 21:02.

  2. #2
    Join Date
    Aug 2003
    Posts
    21
    Or alternatively, it would solve my problem if there is a way to say, in effect, that I want Oracle to sum the values, but only for those rows that meet some condition.

  3. #3
    Join Date
    Jan 2004
    Location
    Singapore
    Posts
    89

    Lightbulb

    Quote Originally Posted by Pithecanthropus
    Or alternatively, it would solve my problem if there is a way to say, in effect, that I want Oracle to sum the values, but only for those rows that meet some condition.
    I think you have to make use of inline views here
    Thanks and Regards,

    Praveen Pulikunnu

  4. #4
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    Somthing like this should work

    Code:
    Select d, Sum(a)
    From (Select b, c, d, Sum(a) a
             From Table
             Where Conditions
             Group By b, c, d)
    Where b = ??
       and c in (...)
    Group By d

  5. #5
    Join Date
    Aug 2003
    Posts
    21

    Thanks for the responses so far.

    I was able to make it work with an inline view. It's the approach I was taking originally, but needed to revise the design a bit to make work.

Posting Permissions

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