Question 258119 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL : Find Differences between columns and only till 12 rows and then sum the differences

I am looking for SQL to Find Differences between columns and only till 12 rows and then sum the differences. For example this is the table I have TAB_PAYOUT. Column DIFFERENCE is calculated by substracting the value of column PAY with the next row. My requirement is to find the difference till 13th row and sum the differences . This table can have more than 12 rows.

This is so far I have tried but not sure how to stop calculating the difference at 12 th row in the table

SELECT ID, 
       SUM(Difference) 
FROM ( SELECT ID,
              PAY AS PAY,
              LAG(PAY,1,0) OVER (ORDER BY ID) previous_row,
              LEAD(PAY,1,0) OVER (ORDER BY ID) next_row,
              LAG(PAY, 1, 0) OVER (ORDER BY ID) - PAY AS Difference
       FROM TAB_PAYOUT 
       where ID = '000000003502'
       order by 1 asc
       )
GROUP BY ID

enter image description here

Tags:

2

Answers


  1. 0

    A few things.

    order by 1 asc doesn’t really do anything. They all have the same sort value, so the order is not predictable. it will probably be in some order by insertion, but that isn’t guaranteed. For this to be meaningful, you might want to use the identify column or a date column to sort.

    Also, a SUM of the differences is no different than the difference between the first and last ones in a group. If you want to see the sum of the differences over a span of 12 entries, you could just use LAG to go back 12 instead of going back 1.

    SELECT ID,
              PAY AS PAY,
              LAG(PAY, 12, 0) OVER (ORDER BY ID) - PAY AS Difference
       FROM TAB_PAYOUT 
       where ID = '000000003502'

    If you only want 12 rows, then I guess you can just add LIMIT 12…but if you mean you want every group of 12 calculated separately, I’m not sure. Might not try to do in sql.

    Lastly, you have a GROUP BY ID at end, but only a single ID. If you want to extend this to query all IDs at the same time, you probably need a subquery, which is probably pretty slow.

    Login or Signup to reply.
  2. 0

    SELECT ID,SUM(DIFFERENCE) AS Total_Difference
    FROM (
    SELECT ID
    ,PAY AS PAY
    ,(PAY – LEAD(PAY) OVER (ORDER BY ID)) AS DIFFERENCE
    FROM TAB_PAYOUT
    WHERE ID = ‘000000003502’
    ) subquery
    WHERE ROWNUM <= 12;

    If you want only 12 rows,you can use ROWNUM or LIMIT

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search