MySQL - Subtracting Value from Previous Row, Group By

Subtracting Previous Value by Prior Group - SQL Server

By using a CTE you would be able to get the data ordered, and then select the previous record.

WITH PREVCTE AS (
SELECT  *, 
        ROW_NUMBER() OVER (PARTITION BY SN ORDER BY MyDate) [RowNum] 
FROM @myTable
)
SELECT A.SN, 
       A.MyDate, 
       A.[Value], 
       A.[Value] - COALESCE(B.[Value], A.[Value]) [Consumption]
FROM PREVCTE A LEFT OUTER JOIN PREVCTE B 
    ON   A.RowNum - 1 = B.RowNum AND  
         A.SN = B.SN 

SQL Fiddle Demo

Subtract previous row value to current row

DECLARE @Test TABLE (
    id      int,
    value   int,
    acc_no  int
) 
INSERT @Test(id, value, acc_no)
VALUES
(1, 12, 1),
(2, 14, 1), 
(3, 15, 1),
(4, 10, 2),
(5, 16, 2),
(6, 19, 1),
(7,  7, 3),
(8, 24, 2)

SELECT id, t.value, acc_no, t.value - ISNULL(v.value, 0) AS result
FROM @Test t
    OUTER APPLY (
        SELECT TOP (1) value
        FROM @Test
        WHERE id < t.id
            AND acc_no = t.acc_no
        ORDER by id DESC
    ) v

How can I subtract a previous row in sql?

Assuming you have an ordering column -- say id -- then you can do the following in SQL Server 2012:

select col,
       col - coalesce(lag(col) over (order by id), 0) as diff
from t;

In earlier versions of SQL Server, you can do almost the same thing using a correlated subquery:

select col,
       col - isnull((select top 1 col
                     from t t2
                     where t2.id < t.id
                     order by id desc
                    ), 0)
from t

This uses isnull() instead of coalesce() because of a "bug" in SQL Server that evaluates the first argument twice when using coalesce().

You can also do this with row_number():

with cte as (
      select col, row_number() over (order by id) as seqnum
      from t
     )
select t.col, t.col - coalesce(tprev.col, 0) as diff
from cte t left outer join
     cte tprev
     on t.seqnum = tprev.seqnum + 1;

All of these assume that you have some column for specifying the ordering. It might be an id, or a creation date or something else. SQL tables are inherently unordered, so there is no such thing as a "previous row" without a column specifying the ordering.

---

Related Topics