Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to SUM() each row into another column

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:

  1. Order the Date by desc
  2. Sum each 'Moved Item" per row
  3. Stop the query if the Sum reaches my desired amount
  4. 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?

like image 801
Igoranze Avatar asked Jun 08 '15 09:06

Igoranze


People also ask

How do I sum cells in one column based on another column?

(1) Select the column name that you will sum based on, and then click the Primary Key button; (2) Select the column name that you will sum, and then click the Calculate > Sum. (3) Click the Ok button.

How do I sum values based on criteria in another column in Excel?

If you want, you can apply the criteria to one range and sum the corresponding values in a different range. For example, the formula =SUMIF(B2:B5, "John", C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal "John."

How do I sum across columns and rows in Excel?

To do this: Select the data to sum plus the blank row below the data and the blank column to the right of the data where the totals will display. On the “Home” tab, in the “Editing” group, click the AutoSum button. Totals are calculated and appear in the last row and in the last column of the selected range!

How do I sum each row in SQL?

This is the basic syntax: SELECT SUM(column_name) FROM table_name; If you need to arrange the data into groups, then you can use the GROUP BY clause. The AVG function finds the arithmetic mean for a group of records in a SQL table.


1 Answers

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.

like image 187
Lalit Kumar B Avatar answered Oct 12 '22 00:10

Lalit Kumar B