[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Maths - Delta Query

Posted on 2014-08-07
6
Medium Priority
?
249 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
Comment
Question by:ukerandi
  • 3
  • 2
6 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 40247536
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

by:ukerandi
ID: 40247553
Thanks, But i need Answer for that my formula
Delta means - It is just the difference between any two values.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40247575
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 10

Author Comment

by:ukerandi
ID: 40247582
so that mean my answer is correct?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40247647
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

Open in new window

0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 40247668
>>> so that mean my answer is correct?

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Suggested Courses

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

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

Join & Ask a Question