How to calculate difference between rows with SQL?

user_7858622 0
Viewed 439

I have a task to create a table with the one new column with the difference between ages in current and next row. The ages should be in descending order. Is it possible to do with SQL? I don't know exactly what sql I should use and that wasn't specified in task.

I understand that there should be something in brackets 

SELECT name, age, (...) AS difference
FROM Animals
ORDER BY age DESC;

The table "Animals" I have

id  |  name  | age
 1  | Molly  |  4
 2  | Jackson|  8 
 3  | Wonka  |  38
 4  | Polly  |  7

The result table should look like this:

  name  | age | difference
 Wonka  | 38  |  30
 Jackson|  8  |  1
 Polly  |  7  |  3
 Molly  |  4  |
difference sql
3 Answers

You need lead() function:

SELECT 
  name, 
  age, 
  age - lead(age) over (order by age desc, name) AS difference
FROM Animals
ORDER BY age DESC

See the demo.
Results:

> name    | age | difference
> :------ | --: | ---------:
> Wonka   |  38 |         30
> Jackson |   8 |          1
> Polly   |   7 |          3
> Molly   |   4 |
user_104988280

You would use lag()/lead() and order by:

select a.*,
       (a - lag(age) over (order by age)) as diff
from animals a
order by age desc;

The order by in the lag() does not need to match the order by in the outer query. The first defines "previous row". The second is for the presentation of the data.

user_11440350

You can achieve the desired result using the MAX function, which operates on the corresponding window of rows.

with Animals as (
  select 1 as id, 'Molly' as name, 4 as age union all
  select 2, 'Jackson', 8 union all
  select 3, 'Wonka', 38 union all
  select 4, 'Polly', 7
)
select
  name, age,
  age - max(age) over(
          order by age
          rows between unbounded preceding
          and 1 preceding
        )
from Animals
order by age desc;

Output:

+---------+-----+------------+
|  name   | age | difference |
+---------+-----+------------+
| Wonka   |  38 | 30         |
| Jackson |   8 | 1          |
| Polly   |   7 | 3          |
| Molly   |   4 | NULL       |
+---------+-----+------------+
user_33504280
Related