I have a table that contains a list of numbers:

select SERIAL, CHANGE from TABLE

I get this result:

1    35
2    10
3    -3
4     2
5     9

Now I want to do a running total, starting with a number gotten from another query, say:

select NUMBER from NUMBERLIST

say NUMBER is 44. I want to construct a query that gives me:

0     0    44
1    35    79
2    10    89
3    -3    86
4     2    88
5     9    97

Is that doable in SQL?

CodePudding user response：

Yes, it is doable, but I'm not too sure of the results you want to see.

This for example:

SELECT COLUMN1, (COLUMN1   44) as 'COLUMN2' FROM NUMBERLIST

Should give you two column results. The first containing the original data. The second containing original data 44 which we also named 'COLUMN2'.

If you wanted to just see the data 44 then:

SELECT (COLUMN1   44) as 'COLUMN NAME' FROM NUMBERLIST

I just realised you want to add from a number from another query. In this instance you would need a nested select. e.g.:

SELECT (SELECT NUMBER FROM TABLE) as 'COLUMN1', ((SELECT NUMBER FROM TABLE)   44) AS 'COLUMN2'

CodePudding user response：

Try this:

SELECT 
  SERIAL
, DECODE (SERIAL, 0, 0, CHANGE) AS CHANGE
, SUM (CHANGE) OVER (ORDER BY SERIAL) AS CHANGE_TOTAL
FROM
(
  SELECT SERIAL, CHANGE
  FROM 
  /*
  (
  VALUES
    (1, 35)
  , (2, 10)
  , (3, -3)
  , (4,  2)
  , (5,  9)
  ) 
  */
  TABLE --(SERIAL, CHANGE)
  
    UNION ALL

  SELECT 0 AS SERIAL, NUMBER AS CHANGE 
  FROM 
  --(VALUES 44) 
  NUMBERLIST 
  --(NUMBER)
)

If you uncomment the commented out lines, you may run the statement as is and get the following result:

CodePudding user response：

You can do this by union the two queries together and then cumulative summation using the function sum() over() :

select id , val , SUM(val) over( order by id) As SumVal from (
select 0 As id , number As val from NUMBERLIST
union 
select id , val from Table_1 ) T

The first function or query must return only one line.

the result