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!

sum total

Discussion in 'SQL PL/SQL' started by XMEGA, Oct 8, 2010.

  1. XMEGA

    XMEGA Guest

    I am displaying customers names and there inventory prices(inv_price * ol_quantity) however I need a total sum of all customer prices at the bottom
    ex
    name1 | 50
    name2 | 25
    total amount | $75

    Code (Text):

    DECLARE
        CURSOR abc IS
        SELECT customer.c_last, SUM(inventory.inv_price * order_line.ol_quantity) AS Total, inventory.inv_price, order_line.ol_quantity
            FROM customer, orders, order_line, inventory
                WHERE customer.c_id = orders.c_id AND
                orders.o_id = order_line.o_id AND
                order_line.inv_id = inventory.inv_id AND
                inventory.inv_id > 0
                GROUP BY customer.c_last, inventory.inv_price, order_line.ol_quantity;
                customer_row abc%ROWTYPE;
    BEGIN
           FOR customer_row IN abc LOOP
            DBMS_OUTPUT.PUT_LINE(customer_row.c_last || ' ' || customer_row.Total || ' = ' || (customer_row.inv_price * customer_row.ol_quantity));
        END LOOP;
    END;
    /
     
    I tried SUM(Total) but it can't use that
     
  2. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    Use CUBE at the group by clause. For example,

    Code (SQL):
    SELECT
       deptno,
       job,
       COUNT(*),
       SUM(sal)
    FROM
       emp
    GROUP BY
       CUBE(deptno,job);
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You could try this:

    Code (SQL):
     
    DECLARE
    CURSOR abc IS
    SELECT customer.c_last, SUM(inventory.inv_price * order_line.ol_quantity) AS Total, inventory.inv_price, order_line.ol_quantity
    FROM customer, orders, order_line, inventory
    WHERE customer.c_id = orders.c_id AND
    orders.o_id = order_line.o_id AND
    order_line.inv_id = inventory.inv_id AND
    inventory.inv_id > 0
    GROUP BY customer.c_last, inventory.inv_price, order_line.ol_quantity;
    customer_row abc%ROWTYPE;
    sumtot NUMBER:=0;
    BEGIN
    FOR customer_row IN abc LOOP
    DBMS_OUTPUT.PUT_LINE(customer_row.c_last || ' ' || customer_row.Total || ' = ' || (customer_row.inv_price * customer_row.ol_quantity));
    sumtot:=sumtot+ customer_row.Total ;
    END LOOP;
    dbms_output.put_line('                                                     '||sumtot);
    END;
    /

     
    CUBE may also work but some SQL*Plus functionality behaves differently from within the PL/SQL engine.
     
    XMEGA likes this.
  4. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    Code (SQL):


    CREATE TABLE tab ( NAME VARCHAR2(20), purchase NUMBER(4));

    INSERT INTO tab
         VALUES ('name1', 50);

    INSERT INTO tab
         VALUES ('name2', 25);

    SELECT   NAME, SUM (purchase) Purchase
        FROM tab
    GROUP BY ROLLUP (NAME);

    Name       Purchase
    ---------   ------------
    name1   50
    name2   25
                75

    SELECT   DECODE (GROUPING (NAME), 1, 'Total Amount $', NAME) NAME,
             SUM (purchase)
        FROM tab
    GROUP BY ROLLUP (NAME);

    Name      Purchase
    -------     ----------
    name1   50
    name2   25
    Total Amount $  75
     
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You did also try this from within a PL/SQL block? That is how the original poster is generating his report.