2

I am using PostgreSQL and I want to subtract two records of the same table and use the result in the same query.

Here is the table:

Scores

6
8
9

Result

6
2
1

What I want to do: 

Result = Score(i) - Score(i-1)

In the end I want the sum of these results. sum(result) must be 9 in my example.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Lily
  • 33
  • 1
  • 4

2 Answers2

5

You need some way to determine the sequence of rows in score. There is no "natural order" in a table in a relational database. So I assume you have an id (or a timestamp or something) to order your records by. Or is i guaranteed to be greater in every new row? Then you can just order by i.

The query itself is simple - once you find out about window functions:

SELECT i - lag(i, 1, 0) OVER (ORDER BY id) AS result
FROM   score
ORDER  BY id;

Including an improvement by @Clodoaldo (see comment).

lag(i, 1, 0) OVER (ORDER BY id)

is equivalent to, but more elegant than:

COALESCE(lag(i) OVER (ORDER BY id), 0)

Purpose is to cover the special case of the first row that has no preceding row.
Demo on sqlfiddle.

sum(result) is trivial because it is bound to equal the last i according to your description:

SELECT i
FROM   score
ORDER  BY id DESC
LIMIT  1;
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    +1 But wouldn't it be preferably to use `lag(i, 1, 0)` in instead of the `coalesce`? – Clodoaldo Neto Jun 22 '12 at 13:46
  • @Clodoaldo: I agree, that's simpler. Updated the answer with your idea. – Erwin Brandstetter Jun 22 '12 at 14:25
  • @ErwinBrandstetter well thanks for the explanation but what I meant with the sum is the sum of the column result so here it is 6 + 2 + 1 that what gave 9 but it is coincidence the same as last value of the score Thanks – Lily Jun 23 '12 at 17:34
  • 1
    @Lily: It is not coincidence but a logic consequence - according to your description. Please provide another example if you have different results. – Erwin Brandstetter Jun 25 '12 at 16:53
0

Something like

SELECT SUM(COALESCE(rx.diff,rx.val))
  FROM
(SELECT x.val,
        x.val - lag(x.val) over () as diff
  FROM (SELECT unnest(ARRAY[6,8,9]) as val) AS x) AS rx

Substituting your table select for my unnest which just generates the data as per your example.

Gavin
  • 6,180
  • 3
  • 25
  • 25
  • Thanks for your comment but if I want a table that I do not know it is entries or it has unlimited entries, that means I can not put the array in this form what should I do? or is it impossible to do that ? – Lily Jun 22 '12 at 13:04
  • @Lily: The array in Gavin's example is obviously just to provide example values and not meant to be part of your solution. The one thing missing, though, is an `ORDER BY` clause. Without it, the result of `lag()` is (implementation specific) arbitrary. Also, `COALESCE` is in the wrong place. – Erwin Brandstetter Jun 22 '12 at 13:13