locked
Sum the quantity in Procedure RRS feed

  • Question

  • User-721945135 posted

    Hi,

    I have a procedure that used to sum the quantity, but i have no idea on how to do the sum in the procedure.

    In the procedure, first it will run the sql query to get one week data. Second, sum the quantity from Day 1 until Day 7. Third, it will store the a temporary table instend of a real table (because i need to use the data from temporary to do checking before insert into real table).

    Below here will be the data from sql query, example from 11/04 until 11/10 data:

    Category	Line	Quantity	DateIn	
    A1		LLC	78		11/04/2012
    A1		RTC	49		11/04/2012
    A2		BAA	98		11/04/2012
    A2		KIO	55		11/04/2012
    A1		LLC	78		11/05/2012
    A1		RTC	49		11/05/2012
    A2		BAA	98		11/05/2012
    A2		KIO	55		11/05/2012
    ....
    A1		LLC	66		11/10/2012
    A1		RTC	23		11/10/2012
    A2		BAA	45		11/10/2012
    A2		KIO	90		11/10/2012

    After the quantity sum up, the result only will take the lastest date with total quantity of Day 1 until Day 7 and insert into temporary table as below:

    Category	Line	Quantity	DateIn	
    A1		LLC	1056		11/10/2012
    A1		RTC	2344		11/10/2012
    A2		BAA	1427		11/10/2012
    A2		KIO	1004		11/10/2012

    Any idea / guide to do the sum...? Please kindly advice.

     

    Tuesday, November 6, 2012 10:21 AM

Answers

  • User-366017857 posted

    Try this one

    SELECT emp.Quantity ,emp.Category, mgr.Line
    FROM yourTableName emp, yourTableName mgr
    WHERE emp.Line = mgr.Line

    you can use this to fetch data from your temporary table or add where conditions in same query and change the table name
    to your real table name.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 6, 2012 10:36 AM
  • User269602965 posted

    Using your visible data

    Using Oracle Analytics

    Inner SQL gets the last date in the partition of category and line

    Outer SQL does the rollup math SUMs

    CREATE TABLE TEST_SUM
    (
      CATEGORY  VARCHAR2(8 BYTE),
      LINE      VARCHAR2(8 BYTE),
      QUANTITY  NUMBER(8),
      DATEIN    DATE
    );
    
    SET DEFINE OFF;
    Insert into TEST_SUM Values
       ('A1', 'LLC', 78, TO_DATE('11/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into TEST_SUM Values
       ('A1', 'RTC', 49, TO_DATE('11/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into TEST_SUM Values
       ('A2', 'BAA', 98, TO_DATE('11/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into TEST_SUM Values
       ('A2', 'KIO', 55, TO_DATE('11/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into TEST_SUM Values
       ('A1', 'LLC', 78, TO_DATE('11/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into TEST_SUM Values
       ('A1', 'RTC', 49, TO_DATE('11/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into TEST_SUM Values
       ('A2', 'BAA', 98, TO_DATE('11/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into TEST_SUM Values
       ('A2', 'KIO', 55, TO_DATE('11/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into TEST_SUM Values
       ('A1', 'LLC', 66, TO_DATE('11/10/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into TEST_SUM Values
       ('A1', 'RTC', 23, TO_DATE('11/10/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into TEST_SUM Values
       ('A2', 'BAA', 45, TO_DATE('11/10/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into TEST_SUM Values
       ('A2', 'KIO', 90, TO_DATE('11/10/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    COMMIT;
    
    SELECT
      CATEGORY,
      LINE,
      QUANTITY,
      DATEIN,
      LAST_VALUE(DATEIN) 
         OVER 
        (PARTITION BY CATEGORY, LINE 
         ORDER BY CATEGORY, LINE, DATEIN
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS DATELAST
    FROM
      TEST_SUM
    /  
    
    CATEGORY LINE       QUANTITY DATEIN    DATELAST
    -------- -------- ---------- --------- ---------
    A1       LLC              78 04-NOV-12 10-NOV-12
    A1       LLC              78 05-NOV-12 10-NOV-12
    A1       LLC              66 10-NOV-12 10-NOV-12
    A1       RTC              49 04-NOV-12 10-NOV-12
    A1       RTC              49 05-NOV-12 10-NOV-12
    A1       RTC              23 10-NOV-12 10-NOV-12
    A2       BAA              98 04-NOV-12 10-NOV-12
    A2       BAA              98 05-NOV-12 10-NOV-12
    A2       BAA              45 10-NOV-12 10-NOV-12
    A2       KIO              55 04-NOV-12 10-NOV-12
    A2       KIO              55 05-NOV-12 10-NOV-12
    A2       KIO              90 10-NOV-12 10-NOV-12
    
    12 rows selected.
    
    well that windowing analytic function got the last available date within category and line partition
    
    SELECT DISTINCT
      a.CATEGORY,
      a.LINE,
      SUM(a.QUANTITY) OVER (PARTITION BY a.CATEGORY, a.LINE) AS TOTAL_QUANTITY,
      TO_CHAR(a.DATELAST,'MM/DD/YYYY') AS DATELAST
    FROM
      (
      SELECT
        CATEGORY,
        LINE,
        QUANTITY,
        DATEIN,
        LAST_VALUE(DATEIN) 
           OVER 
          (PARTITION BY CATEGORY, LINE 
           ORDER BY CATEGORY, LINE, DATEIN
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS DATELAST
      FROM
        TEST_SUM
      ) a
    ORDER BY a.CATEGORY, a.LINE  
    / 
    
    CATEGORY LINE     TOTAL_QUANTITY DATELAST
    -------- -------- -------------- ----------
    A1       LLC                 222 11/10/2012
    A1       RTC                 121 11/10/2012
    A2       BAA                 241 11/10/2012
    A2       KIO                 200 11/10/2012
    
    and that completes the mathematics for the final desired result table.
    
    Oracle is fun.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 6, 2012 8:47 PM

All replies

  • User-366017857 posted

    Try this one

    SELECT emp.Quantity ,emp.Category, mgr.Line
    FROM yourTableName emp, yourTableName mgr
    WHERE emp.Line = mgr.Line

    you can use this to fetch data from your temporary table or add where conditions in same query and change the table name
    to your real table name.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 6, 2012 10:36 AM
  • User269602965 posted

    Using your visible data

    Using Oracle Analytics

    Inner SQL gets the last date in the partition of category and line

    Outer SQL does the rollup math SUMs

    CREATE TABLE TEST_SUM
    (
      CATEGORY  VARCHAR2(8 BYTE),
      LINE      VARCHAR2(8 BYTE),
      QUANTITY  NUMBER(8),
      DATEIN    DATE
    );
    
    SET DEFINE OFF;
    Insert into TEST_SUM Values
       ('A1', 'LLC', 78, TO_DATE('11/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into TEST_SUM Values
       ('A1', 'RTC', 49, TO_DATE('11/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into TEST_SUM Values
       ('A2', 'BAA', 98, TO_DATE('11/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into TEST_SUM Values
       ('A2', 'KIO', 55, TO_DATE('11/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into TEST_SUM Values
       ('A1', 'LLC', 78, TO_DATE('11/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into TEST_SUM Values
       ('A1', 'RTC', 49, TO_DATE('11/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into TEST_SUM Values
       ('A2', 'BAA', 98, TO_DATE('11/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into TEST_SUM Values
       ('A2', 'KIO', 55, TO_DATE('11/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into TEST_SUM Values
       ('A1', 'LLC', 66, TO_DATE('11/10/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into TEST_SUM Values
       ('A1', 'RTC', 23, TO_DATE('11/10/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into TEST_SUM Values
       ('A2', 'BAA', 45, TO_DATE('11/10/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into TEST_SUM Values
       ('A2', 'KIO', 90, TO_DATE('11/10/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    COMMIT;
    
    SELECT
      CATEGORY,
      LINE,
      QUANTITY,
      DATEIN,
      LAST_VALUE(DATEIN) 
         OVER 
        (PARTITION BY CATEGORY, LINE 
         ORDER BY CATEGORY, LINE, DATEIN
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS DATELAST
    FROM
      TEST_SUM
    /  
    
    CATEGORY LINE       QUANTITY DATEIN    DATELAST
    -------- -------- ---------- --------- ---------
    A1       LLC              78 04-NOV-12 10-NOV-12
    A1       LLC              78 05-NOV-12 10-NOV-12
    A1       LLC              66 10-NOV-12 10-NOV-12
    A1       RTC              49 04-NOV-12 10-NOV-12
    A1       RTC              49 05-NOV-12 10-NOV-12
    A1       RTC              23 10-NOV-12 10-NOV-12
    A2       BAA              98 04-NOV-12 10-NOV-12
    A2       BAA              98 05-NOV-12 10-NOV-12
    A2       BAA              45 10-NOV-12 10-NOV-12
    A2       KIO              55 04-NOV-12 10-NOV-12
    A2       KIO              55 05-NOV-12 10-NOV-12
    A2       KIO              90 10-NOV-12 10-NOV-12
    
    12 rows selected.
    
    well that windowing analytic function got the last available date within category and line partition
    
    SELECT DISTINCT
      a.CATEGORY,
      a.LINE,
      SUM(a.QUANTITY) OVER (PARTITION BY a.CATEGORY, a.LINE) AS TOTAL_QUANTITY,
      TO_CHAR(a.DATELAST,'MM/DD/YYYY') AS DATELAST
    FROM
      (
      SELECT
        CATEGORY,
        LINE,
        QUANTITY,
        DATEIN,
        LAST_VALUE(DATEIN) 
           OVER 
          (PARTITION BY CATEGORY, LINE 
           ORDER BY CATEGORY, LINE, DATEIN
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS DATELAST
      FROM
        TEST_SUM
      ) a
    ORDER BY a.CATEGORY, a.LINE  
    / 
    
    CATEGORY LINE     TOTAL_QUANTITY DATELAST
    -------- -------- -------------- ----------
    A1       LLC                 222 11/10/2012
    A1       RTC                 121 11/10/2012
    A2       BAA                 241 11/10/2012
    A2       KIO                 200 11/10/2012
    
    and that completes the mathematics for the final desired result table.
    
    Oracle is fun.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 6, 2012 8:47 PM