sum of differencies of rows

Question

I have example values in column like this:

The values are rising up and then fall down to 0 and rising up again. I need to count differencies between rows in new column and the sum of these differencies too (cumulative sum) for all values. The values 56 and 5 are new differencies from zero
The sum is 819. Example from bottom> (23-2)+(43-23)+(109-43)+..+(654-643)+(5)+(23-5)+..

Does the table have an Identity field or a "datetime entered" field so that the order is set. (There is an implicit order but it's good to have an explicit one.) — Holger Brandt, Jun 14 '12 at 20:32
If you are getting the difference in value between the rows then how are you ordering the rows? — Kyra, Jun 14 '12 at 20:35
You really need to answer comments to clarify your question. As it is, it can be closed as "not a real question" — Lamak, Jun 14 '12 at 20:42
@Lamak - It's the order in which the record was added (assuming no index which is what I see here). The first record added is the first record of the table. The second added, the second. And so on. It's not a good idea to have a non-indexed table. — Holger Brandt, Jun 14 '12 at 20:48
@HolgerBrandt - That implicit order doesn't exist, you can obtain a different order on your set if there is no `ORDER BY`. — Lamak, Jun 14 '12 at 20:49
@HolgerBrandt I'm afraid you have been lulled into believing some myth about how SQL Server works. There is no such thing as "first" row in a table. By definition, a table is an unordered set of rows. Even with a clustered index (which can't possibly be dictating the above order anyway, unless there are columns missing), there is no magic physical order to a table. You apply order using an ORDER BY clause - anything else, and SQL Server is free to return data in any order (and it won't always come back the way you seem to think). — Aaron Bertrand, Jun 14 '12 at 20:52
OK if Im understanding this correctly you want to take the difference between the two columns, group on that difference and the count them some thing like this `SELECT (v.value - v.differencies) AS "diff", COUNT(*) AS "count" FROM v GROUP BY diff;` — Samy Vilar, Jun 14 '12 at 20:53
@AaronBertrand - I stand corrected. I found this as well: [http://stackoverflow.com/questions/2040724/how-does-sql-server-sort-your-data] — Holger Brandt, Jun 14 '12 at 20:54
possible duplicate of [How do i create a query that will give me a cumulative total?](http://stackoverflow.com/questions/8904750/how-do-i-create-a-query-that-will-give-me-a-cumulative-total) — John Conde, Jun 17 '12 at 23:35

Holger Brandt · Answer 1 · 2012-06-14T22:08:05.073

Okay, here is my try. However, you need to add an Identity field (which I called "AddSequence") that starts with 1 for the first value ("2") and increments by one for every other value.

SELECT SUM(C.Diff) FROM
(
 SELECT CASE WHEN (A.[Value] - (SELECT [Value] FROM [TestValue] AS B WHERE B.[AddSequence]= A.[AddSequence]-1)) > 0
    THEN (A.[Value] - (SELECT [Value] FROM [TestValue] AS D WHERE D.[AddSequence]= A.[AddSequence]-1))
    ELSE 0
    END AS Diff
 FROM [TestValue] AS A
) AS C

The first solution I had neglected that fact that we had to start over whenever the difference was negative.

score 0 · Answer 2 · answered Jun 14 '12 at 20:31

0

I think you are looking for something like:

SELECT SUM(a - b)) as sum_of_differences
FROM ...

answered Jun 14 '12 at 20:31

bluevector

3,485
1
15
18

2

Just, don't use `left` and `right` as column names, as they are usually reserved words – Lamak Jun 14 '12 at 20:31
I think he wants the difference between one row and the next, but the question is not very clear. – Mark Byers Jun 14 '12 at 20:33
@MarkByers Ah... that would change the answer considerably, wouldn't it. – bluevector Jun 14 '12 at 20:34
Thats it. Difference between one row and the next. There is only one column. The column "differencies" is for simpler explanation. Data are ordered by time. It is one variable increased in time. From time to time variable drops to zero and counting starts again, but I need the cumulative sum of differences. – user1457132 Jun 14 '12 at 21:21

user1166147 · Answer 3 · 2012-06-15T22:43:31.053

EDITED - BASED OFF OF YOUR QUESTION EDIT (T-SQL) I don't know how you can do this without adding an Id. If you ad an Id - this gives the exact output you had posted before your edit. There's probably a better way, but this is quick and dirty - for a one time shot. Using a SELF JOIN. Differences was the name of your new column originally.

UPDATE A
SET differences = CASE WHEN A.[values] > B.[Values] THEN A.[values] -  B.[Values]
ELSE A.[values] END
FROM SO_TTABLE A
JOIN SO_TTABLE B ON A.ID = (B.ID - 1)

OUTPUT

Select [Values], differences FROM SO_TTABLE 


   [values]     differences   
    ------------------------
    89          24             
    65           9            
    56          56             
    78           4             
    74           1            
    73          28             
    45          22             
    23          18              
     5           5              
   654          11           
   643         100            
   543         198           
   345          90            
   255          22            
   233         124            
   109          66             
    43          20             
    23          21              
     2           0

Samy Vilar · Answer 4 · 2012-06-14T22:34:03.237

I think you want this for the differences, I've tested it in sqlite

SELECT CASE WHEN (v.value - val) < 0 THEN 0 ELSE (v.value - val) END AS differences
FROM v,
(SELECT rowid, value AS val FROM v WHERE rowid > 1) as next_val
WHERE v.rowid = next_val.rowid - 1

as for the sums

SELECT SUM(differences) FROM
(
    SELECT CASE WHEN (v.value - val) < 0 THEN 0 ELSE (v.value - val) END AS differences
    FROM v,
    (SELECT rowid, value AS val FROM v WHERE rowid > 1) AS next_val
    WHERE v.rowid = next_val.rowid - 1
)

sum of differencies of rows

4 Answers4

Linked