CODEWITHSUNDEEP
sqlite
Username
Username

Reputation: 3663

SQLite: How do I subtract the value in one row from another?

Here is my example table:

column_example
10
20
25
50


Here is what I would like:

column_example2
10
5
25


I'm sure this is a simple question, but I haven't found the answer in the SQLite Syntax web page or via Google.

EDIT: To clarify, the code would likely return the outputs for: 20-10 25-20 50-25

Upvotes: 2

Views: 7753

Answers (1)

biziclop
biziclop

Reputation: 14616

This solution might be slow, but I had to consider the potential gaps between succeeding rowids:

http://sqlfiddle.com/#!5/daeed/1

SELECT
  (SELECT x
   FROM t AS t3
   WHERE t3.rowid =
     (SELECT MIN(tt.rowid)
      FROM t AS tt
      WHERE tt.rowid > t.rowid
     )
  )
  - x

FROM t
WHERE diff IS NOT NULL

If it is guaranteed to not have any gaps between rowids, then you can use this simpler query:

http://sqlfiddle.com/#!5/1f906/3

SELECT t_next.x - t.x
FROM       t
INNER JOIN t AS t_next
        ON t_next.rowid = t.rowid + 1

Upvotes: 7

Related Questions