Solved

# Maths - Delta Query

Posted on 2014-08-07
216 Views
Last Modified: 2014-08-08
Hi
I need to find this number series Total Delta
25,      24,      24,      25,      25

My answer is 0, i just wondering is correct or not.
24-25= -1
24-24  = 0
25-24 = 1
25-25 =0

After Add all together to find Delta i got = 0
0
Question by:ukerandi
6 Comments

LVL 73

Expert Comment

Not sure exactly why you want it as sql but it's fairly easy to do....

WITH yourdata
AS (SELECT 1 id, 25 n FROM DUAL
UNION ALL
SELECT 2, 24 FROM DUAL
UNION ALL
SELECT 3, 24 FROM DUAL
UNION ALL
SELECT 4, 25 FROM DUAL
UNION ALL
SELECT 5, 25 FROM DUAL)
SELECT SUM(diff)
FROM (SELECT n - LAG(n) OVER (ORDER BY id) diff FROM yourdata);

Assuming your data is in a table somewhere, simply remove the WITH clause and change yourdata to your table and the other columns as appropriate
0

LVL 10

Author Comment

Thanks, But i need Answer for that my formula
Delta means - It is just the difference between any two values.
0

LVL 73

Expert Comment

I know what a delta is

n - LAG(n) OVER (ORDER BY id)   --- there's your delta

but you asked for the sum of them   so sum in the outer query

and that's 0
0

LVL 10

Author Comment

so that mean my answer is correct?
0

LVL 47

Expert Comment

yes

A variant on that sql that displays the details; the "LAG(N)OVER(ORDERBYID)" column is the n of the previous row.
The column "DIFF" when summed = 0

``````| LAG(N)OVER(ORDERBYID) |  N |   DIFF |
|-----------------------|----|--------|
|                (null) | 25 | (null) |
|                    25 | 24 |     -1 |
|                    24 | 24 |      0 |
|                    24 | 25 |      1 |
|                    25 | 25 |      0 |

WITH yourdata
AS (
SELECT 1 id, 25 n FROM DUAL
UNION ALL
SELECT 2, 24 FROM DUAL
UNION ALL
SELECT 3, 24 FROM DUAL
UNION ALL
SELECT 4, 25 FROM DUAL
UNION ALL
SELECT 5, 25 FROM DUAL
)
SELECT
LAG(n) OVER (ORDER BY id)
, n
, n - LAG(n) OVER (ORDER BY id) diff
FROM yourdata
;

http://sqlfiddle.com/#!4/d41d8/33755
``````
0

LVL 73

Accepted Solution

>>> so that mean my answer is correct?

yes, your answer is correct per your description of the operations
0

## Join & Write a Comment Already a member? Login.

### Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This is a research brief on the potential colonization of humans on Mars.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

#### 729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!