Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    7

    Unanswered: Totalling a Column

    Hello.

    I am creating a view and I want to list the values in a column, and at the bottom of the Report I Want to total the column, is it possible to do this in one select statement...

    IE..

    REGION QUANTITY
    --------- ------------
    1 10
    2 20
    TOTAL 30

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Totalling a Column

    Well, a view is not a report. However, you could do this:

    create view v
    select 1 as seq, region, quantity from t
    union all
    select 2 as seq, 'TOTAL' as region, sum(quantity) as quantity from t;

    The seq column is optional: it ensures that TOTAL comes after all REGION values, even after 'ZIMBABWE':

    select region, quantity
    from v
    order by seq, region;

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Totalling a Column

    Of course, in a SQL Plus report you don't need to create that view. Just do this:

    compute sum label TOTAL of quantity on report
    break on report

    select region, quantity from t order by region;

  4. #4
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106
    Possibly a candidate for rollup or cube:
    http://download-west.oracle.com/docs...1b.htm#2066444
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by dbtoo2001
    Possibly a candidate for rollup or cube:
    http://download-west.oracle.com/docs...1b.htm#2066444
    Good point! Somehow I never think of those options...

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Rollup is the same as analytics?

    PHP Code:
    select regionquantitysum(quantityover () total_quantity
    from table_name

    for the last column you will have the total regardless
    of what row is selected.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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