CODEWITHSUNDEEP
sqldifference
lisa
lisa

Reputation: 67

How to calculate difference between rows with SQL?

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  |

Upvotes: 5

Views: 2016

Answers (3)

Andrei Odegov
Andrei Odegov

Reputation: 3429

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       |
+---------+-----+------------+

Upvotes: 0

forpas
forpas

Reputation: 164054

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 |

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 2

Related Questions