How to SUM() each row into another column

Question

I have this table

 | ID_prim    | ID (FKey)   | Date         | Moved Items  |
 |:-----------|:------------|-------------:|:------------:|
 | 1003       | 12_1        |    nov 2013  |    2         |
 | 1003       | 12_2        |    okt 2013  |    3         |
 | 1003       | 12_3        |    dec 2014  |    5         |
 | 1003       | 12_4        |    feb 2015  |    10        |
 | 1003       | 12_5        |    apr 2012  |    1         |
 | 1003       | 12_11       |    jan 2011  |    5         |

I want to query the same table as follows:

Order the Date by desc
Sum each 'Moved Item" per row
Stop the query if the Sum reaches my desired amount
My desired amount starts from the MAX 'Summed Total' (26) and subtracts the amount I want (16)

Like so

| ID_prim    | ID (FKey)   | Date         | Moved Items  | Summed Total |
|:-----------|:------------|-------------:|:------------:|:------------:|
| 1003       | 12_4        |    feb 2015  |     10       |     26
| 1003       | 12_3        |    dec 2014  |     5        |     16
| 1003       | 12_3        |    nov 2013  |     2        |     11 <
| 1003       | 12_4        |    okt 2013  |     3        |     9 
| 1003       | 12_5        |    apr 2012  |     1        |     6
| 1003       | 12_11       |    jan 2011  |     5        |     5

I want to stop the query when i reach "Summed Total" (26) - 16 = 10. So Show me everything from 10 > I would only get these values in the database.

| ID_prim    | ID (FKey)   | Date         | Moved Items  | Summed Total |
|:-----------|:------------|-------------:|:------------:|:------------:|
| 1003       | 12_4        |    feb 2015  |     10       |     26
| 1003       | 12_3        |    dec 2014  |     5        |     16
| 1003       | 12_3        |    nov 2013  |     2        |     11

What I have is the following

  SELECT
  T1.ID_prim, T1.ID as ID (FKey), T1.Moved_Items as Moved Items, t1.Date, SUM(T2.MOVEMENTQTY) AS Summed Total
  FROM Table1 T1
  INNER JOIN Table1 T2 ON T2.ID <= T1.ID
    inner join table2 inout on T1.ID_prim = inout.ID_prim
    AND T2.ID_prim = inout.ID_prim
    AND T2.ID_prim = T1.ID_prim
  where t1.ID_prim = 1003
  and t2.ID_prim = 1003
  and inout.ISSOTRX = 'N'
  GROUP BY T1.ID_prim, T1.Moved Items, t1.Date
  HAVING SUM(T2.Moved Items) <= 16
  order by  t1.UPDATED desc

But the sum doesn't really work. Can anyone help me out to make the SQL statement for Oracle DB that will print my Desired table?

`nov 2013` is NOT a **DATE**. Please provide some sample data using `to_date` for the date values. — Lalit Kumar B, Jun 08 '15 at 10:19
@LalitKumarB I didn't write out the date above. In the DB my date column looks like this "2015-06-08 11:52:00" and it's a DATE value. But that is not the main focus here. The main focus is the Summed Total and how to iterate — Igoranze, Jun 08 '15 at 10:24
@LalitKumarB No it should not. remeber that we first count up, and the substract the desired value to show the result from MAX to MIN until we Substract all rows to get the desired value. That said. 16 + 10 = 26, — Igoranze, Jun 08 '15 at 10:34
Why not? If not then what is the rule? The sum of 2, 3, 6, 10, 1, 5 is 27 not 26. — Lalit Kumar B, Jun 08 '15 at 10:37
I see where you get that, The first table has a value that is 6, this should be 5. Fixed — Igoranze, Jun 08 '15 at 10:38

Lalit Kumar B · Accepted Answer · 2015-06-08T12:12:18.307

Based on OP's clarifications via comments on the question, it could be done using SUM() analytic function to get the running total, and then filter it based on the condition.

Table:

SQL> SELECT * FROM t;

   ID_PRIM ID    DT             MOVED
---------- ----- --------- ----------
      1003 12_1  01-NOV-13          2
      1003 12_2  01-OCT-13          3
      1003 12_3  01-DEC-14          5
      1003 12_4  01-FEB-15         10
      1003 12_5  01-APR-12          1
      1003 12_11 01-JAN-11          5

6 rows selected.

SQL>

Running total

SQL> SELECT t.*, SUM(moved) OVER(ORDER BY dt) sm FROM t ORDER BY dt DESC;

   ID_PRIM ID    DT             MOVED         SM
---------- ----- --------- ---------- ----------
      1003 12_4  01-FEB-15         10         26
      1003 12_3  01-DEC-14          5         16
      1003 12_1  01-NOV-13          2         11
      1003 12_2  01-OCT-13          3          9
      1003 12_5  01-APR-12          1          6
      1003 12_11 01-JAN-11          5          5

6 rows selected.

SQL>

Desired output

SQL> WITH DATA AS
  2    ( SELECT t.*, SUM(moved) OVER(ORDER BY dt) sm FROM t ORDER BY dt DESC
  3    )
  4  SELECT * FROM data WHERE sm >= 16;

   ID_PRIM ID    DT             MOVED         SM
---------- ----- --------- ---------- ----------
      1003 12_4  01-FEB-15         10         26
      1003 12_3  01-DEC-14          5         16

SQL>

Please note that, nov 2013 is not a date, it is a string. Since you want to sort on the basis of date, you must always use TO_DATE to explicitly convert it into date. Anyway, I used TO_DATE to create the sample data.

Update OP wants to subtract his desired value from the MAX value of the summed up values at run time.

SQL> WITH DATA AS
  2    ( SELECT t.*, SUM(moved) OVER(ORDER BY dt) sm FROM t ORDER BY dt DESC
  3    )
  4  SELECT * FROM DATA t WHERE sm >
  5    (SELECT MAX(sm) FROM data
  6    ) - 16 ;

   ID_PRIM ID    DT             MOVED         SM
---------- ----- --------- ---------- ----------
      1003 12_4  01-FEB-15         10         26
      1003 12_3  01-DEC-14          5         16
      1003 12_1  01-NOV-13          2         11

SQL>

In the updated query, MAX(sm) returns 26, and then the rows are filtered on the condition WHERE sm > MAX(sm) -16 which means return all the rows where the 'sm' value is greater than 26 -16 i.e. 10. You could use a substitution variable to input the value 16 at run time.

Close, but i dont want to start from 0 and then count up and check from where my >= 16 starts. I want to first count them all like in the 'Running total' table, and then from the max value (26) substract my desired value (16). What would happen if another row will be added above the ID_PRIM ID DT MOVED SM ---------- ----- --------- ---------- ---------- 1003 12_9 02-FEB-15 10 36 1003 12_4 01-FEB-15 10 26 then my "Desired output" will show the last added value aswell. Where i then want to start from MAX(36) and substract — Igoranze, Jun 08 '15 at 11:33
@Igoranze Ok, see my update. In the updated query, MAX(sm) returns 26, and then the rows are filtered on the condition WHERE sm > MAX(sm) -16 which means return all the rows where the 'sm' value is greater than 26 -16 i.e. 10. You could use a substitution variable to input the value 16 at run time. — Lalit Kumar B, Jun 08 '15 at 12:12

How to SUM() each row into another column

1 Answers1