How to Get Difference Between Two Rows For a Column Field

How to get difference between two rows for a column field?

SELECT
   [current].rowInt,
   [current].Value,
   ISNULL([next].Value, 0) - [current].Value
FROM
   sourceTable       AS [current]
LEFT JOIN
   sourceTable       AS [next]
      ON [next].rowInt = (SELECT MIN(rowInt) FROM sourceTable WHERE rowInt > [current].rowInt)

EDIT:

Thinking about it, using a subquery in the select (ala Quassnoi's answer) may be more efficient. I would trial different versions, and look at the execution plans to see which would perform best on the size of data set that you have...

EDIT2:

I still see this garnering votes, though it's unlikely many people still use SQL Server 2005.

If you have access to Windowed Functions such as LEAD(), then use that instead...

SELECT
  RowInt,
  Value,
  LEAD(Value, 1, 0) OVER (ORDER BY RowInt) - Value
FROM
  sourceTable

How to get difference between two rows for a column field? When the column is a timestamp

Since you are on SQL Server 2012, you can now use LAG/LEAD to get a value of a column from the previous/next row. Then you just calculate the difference as you would between two columns on the same row. Something like this:

create table YOUR_TABLE(ID integer, DT datetime);
insert into YOUR_TABLE
    select 2, '02/02/2016 12:00:00' union all
    select 3, '02/05/2016 12:00:00' union all
    select 4, '02/06/2016 12:00:00' union all
    select 5, '02/07/2016 12:00:00' 
;

select 
    ID,
    DT,
    datediff(day, DT, lead(DT) over (order by ID)) as DIFF
from your_table;

How to calculate difference between two rows in SQL?

SQLPlus is usually attached to Oracle, which supports the lag() function. So, you can do what you want pretty easily:

select tname, tdate, score,
       coalesce(score - lag(score) over (order by tdate), 0) as diff
from tournaments natural inner join 
     performances
where bname = 'Fred'
order by tdate;

How to get the difference between consecutive rows in MySQL?

For MySQL 8 then use Lag window function.

SELECT 
    test.id, 
    test.date, 
    test.number, 
    test.qty, 
    IFNULL(test.number - LAG(test.number) OVER w, 0) AS diff,
    ROUND(IFNULL(test.number - LAG(test.number) OVER w, 0)/ test.qty, 2) AS 'Avg'
FROM purchases test
WINDOW w AS (ORDER BY test.`date` ASC);

For MySQL 5.7 or lesser version

We can use the MySQL variable to do this job. Consider your table name is test.

SELECT 
    test.id, 
    test.date, 
    test.number, 
    test.qty, 
    @diff:= IF(@prev_number = 0, 0, test.number - @prev_number) AS diff,
    ROUND(@diff / qty, 2) 'avg',
    @prev_number:= test.number as dummy
FROM 
    test, 
    (SELECT @prev_number:= 0 AS num) AS b
ORDER BY test.`date` ASC;

-------------------------------------------------------------------------------
Output:

| id    | date          | number| qty   | diff  | avg   | dummy | 
-----------------------------------------------------------------
| 114   | 2018-10-07    | 200   | 5     | 0     | 0.00  | 200   |   
| 120   | 2018-12-01    | 300   | 10    | 100   | 10.00 | 300   |   
| 123   | 2019-02-03    | 700   | 12    | 400   | 33.33 | 700   |  
| 1126  | 2019-03-07    | 1000  | 15    | 300   | 20.00 | 1000  |

Explaination:

• (SELECT @prev_number:= 0 AS num) AS b
  we initialized variable @prev_number to zero in FROM clause and joined with each row of the test table.
• @diff:= IF(@prev_number = 0, 0, test.number - @prev_number) AS diff First we are generating difference and then created another variable diff to reuse it for average calculation. Also we included one condition to make the diff for first row as zero.
• @prev_number:= test.number as dummy we are setting current number to this variable, so it can be used by next row.

Note: We have to use this variable first, in both difference as well as average and then set to the new value, so next row can access value from the previous row.

You can skip/modify order by clause as per your requirements.

How to calculate difference between two rows in a date interval?

I came up with this query that solved the problem, it wasn't that simple

SELECT
 ItmDtIni.PO
,ItmDtIni.POItem AS [PO Item]
,ROUND(ItmDtIni.QtyPending - ItmDtEnd.QtyPending, 3) AS [Qty Delivered]
,ROUND((ItmDtIni.QtyPending - ItmDtEnd.QtyPending) * ItmDtEnd.Price, 2) AS [Value delivered(US$)]
//Filtering subqueries to bring only the items in the date interval to make a self join
FROM (((SELECT
        PO
       ,POItem
       ,QtyPending
       ,MIN(Date) AS MinDate
       FROM Item
       WHERE Date BETWEEN FORMAT(begin_date, 'dd/mm/yyyy') AND FORMAT(end_date, 'dd/mm/yyyy')
       GROUP BY
        PO
       ,POItem
       ,QtyPending) AS ItmDtIni
//Self join filtering to bring only items in the date interval with the previously filtered table
INNER JOIN (SELECT
            PO
           ,POItem
           ,QtyPending
           ,Price
           ,MAX(Date) AS MaxDate
           FROM Item
           WHERE Date BETWEEN FORMAT(begin_date, 'dd/mm/yyyy') AND FORMAT(end_date, 'dd/mm/yyyy')
           GROUP BY
            PO
           ,POItem
           ,QtyPending
           ,Price) AS ItmDtEnd
  ON ItmDtIni.PO = ItmDtEnd.PO
  AND ItmDtIni.POItem = ItmDtEnd.POItem)
INNER JOIN PO
  ON ItmDtEnd.PO = PO.Numero)
WHERE
//Showing only items that had a variation in the date interval
      ROUND(ItmDtIni.QtyPending - ItmDtEnd.QtyPending, 3) <> 0
//Anchoring min date in the interval for each item found by the first subquery
      AND ItmDtIni.MinDate = (SELECT MIN(Item.Date)
                              FROM Item
                              WHERE
                                    ItmDtIni.PO = Item.PO
                                AND ItmDtIni.POItem = Item.POItem
                                AND Date BETWEEN FORMAT(begin_date, 'dd/mm/yyyy') AND FORMAT(end_date, 'dd/mm/yyyy'))
//Anchoring max date in the interval for each item found by the second subquery  
  AND ItmDtEnd.MaxDate = (SELECT MAX(Item.Date)
                          FROM Item
                          WHERE
                                ItmDtEnd.PO = Item.PO
                            AND ItmDtEnd.POItem = Item.POItem
                            AND Date BETWEEN FORMAT(begin_date, 'dd/mm/yyyy') AND FORMAT(end_date, 'dd/mm/yyyy'))

Difference between two rows in bigquery

You can use LAG to get the value of the previous row in BigQuery:

SELECT 
  STOREID,
  MEMBERSHIP_TYPE,
  yyyy_mm,
  SUM_MONTHLY_SPEND, 
  IFNULL(LAG(SUM_MONTHLY_SPEND) OVER (PARTITION BY STOREID ORDER BY yyyy_mm ASC) - SUM_MONTHLY_SPEND, 0) AS MONTHLY_SPEND_DIFF
FROM dataset.table

How to get the difference between two rows in a table and place in a new column through SQL

You have specified multiple RDBMS. For oracle, a straightforward query would be

    SELECT e_id
         , e_d - NVL(LAG ( e_d ) OVER ( ORDER BY e_d ), e_d) diff
      FROM events
         ;

assuming a base table events created by

    CREATE TABLE events ( e_id NUMBER PRIMARY KEY, e_d DATE );

The difference will be presented in the unit 'days'.

An alternative query does not use the LAG function and - while stillbeing formulated in oracle syntax - should be portable:

    SELECT e.e_id
         , NVL ( e.e_d - elagged.e_d, 0 ) diff
      FROM events e
 LEFT JOIN events elagged  ON ( elagged.e_id = e.e_id - 1 )
  ORDER BY e.e_id
         ;

This sqlfiddle contains the complete example.

---

Related Topics