1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL sum table

Discussion in 'General' started by virusx1984, Mar 4, 2011.

  1. virusx1984

    virusx1984 Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    Q:I have a simple table_1 like:
    pn|amount
    A|1000
    B|2000
    C|3000


    I want to sum (A,B,C) and place the result after row of pn of C :

    pn|amount
    A|1000
    B|2000
    C|3000
    total|6000

    Do I have to create a query_1 (select 'toatl',sum(amount) from table_1) , and then use "union all" to combine it with table_1 to get the result I want?

    Is there a simpler way to do that?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    If you're using SQL*Plus there is:

    Code (SQL):
     
    break ON report
    compute SUM OF amount ON report
     
    SELECT pn, amount
    FROM mytable;
     
     
    The output will look like this:
    Code (SQL):
     
     
    PN     AMOUNT
    ==== ======
    A          1000
    B          2000
    C          3000
            --------
    SUM       6000
     
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    You can try something like this.

    Code (Text):
    SQL> select * from tab_1;

    PN      AMOUNT
    --- ----------
    A         1000
    B         2000
    C         3000

    SQL> SELECT  NVL(PN,'TOTAL') PN,
            SUM(AMOUNT) AMOUNT
    FROM TAB_1
    GROUP BY ROLLUP(PN)  2    3    4  ;

    PN        AMOUNT
    ----- ----------
    A           1000
    B           2000
    C           3000
    TOTAL       6000

    SQL>