I have a table in the format :

SomeID  SomeData

1       3
2       7
3       9
4       10
5       14
6       16
.       .
.       .

I want to find sum of difference between rows in this table. i.e ( (7-3) (10-9) (16-14) ....)

Which is the best way to do this

CodePudding user response：

You can try to use ROW_NUMBER window function to make a serial number then MOD by 2 to get your expected group then use condition aggregate function.

Query 1:

SELECT SUM(CASE WHEN rn = 0 THEN SomeData END) - SUM(CASE WHEN rn = 1 THEN SomeData END) 
FROM (
  SELECT SomeData,ROW_NUMBER() over(order by SomeID) % 2 rn
  FROM t t1
) t1

Results:

|   |
|---|
| 7 |

CodePudding user response：

Using a self join along with the modulus:

SELECT SUM(t1.SomeData - t2.SomeData) AS total_diff
FROM yourTable t1
INNER JOIN yourTable t2
    ON t1.SomeID = t2.SomeID   1
WHERE t1.SomeID % 2 = 0;

Demo

This answer assumes that the SomeID sequence in fact starts with 1 and increments by 1 with each subsequent row. If not, then we might be able to first apply ROW_NUMBER over SomeID and generate a 1 to N sequence.

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY SomeID) rn
    FROM yourTable
)

SELECT SUM(t1.SomeData - t2.SomeData) AS total_diff
FROM cte t1
INNER JOIN cte t2
    ON t1.SomeID = t2.SomeID   1
WHERE t1.rn % 2 = 0;