Question 113878 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Get sum of difference of rows in sql

I have a table with given values

| id  | level  | value |
| --- | ------ | ----- |
| 1   | 30000  | 0.05  |
| 2   | 100000 | 0.06  |
| 3   | 120000 | 0.07  |

I want to create an postgres sql query to get a sum in the following logic. I will be providing a value (level) as as parameter to the query (170000).

(100000- 30000)*0.05 + (120000-100000)*0.06 + (170000-120000)*0.07

(difference of level of row2 and row1) * value of row1 +
(difference of level of row3 and row2) * value of row2 +
(difference of level as input and row3) * value of row3

Tags:

2

Answers


  1. 0

    You may use LEAD function to get the next level value, and find the sum according to your logic as the following:

    SELECT SUM((nextLevel-level)*value) AS res
FROM
(
  SELECT *,
    LEAD(LEVEL, 1, 170000) OVER (ORDER BY id) nextLevel
  FROM tbl
) T

    See a demo.

    Login or Signup to reply.
  2. 0

    Use LEAD to see the next row’s value. Use COALESCE to substitute the last missing value with 170000.

    select sum(subtotal) as total
from
(
  select
    (coalesce(lead(level) over (order by id), 170000) - level) * value as subtotal
  from mytable
) subtotals;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search