CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Gopal
Gopal

Reputation: 11972

How to get a difference between two rows

I want to find the difference between two rows on the same column group by id

ID Value1 Value2

a  500 200
b  300 200
a  100 300
b  300 400
....

Expected output

ID Value1 Value2

a  400 -100
b  0 -200
....

How to make a query for the above condition.

Upvotes: 2

Views: 6472

Answers (4)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16894

Use option with CTE and ROW_NUMBER() ranking function

 ;WITH cte AS
 (
  SELECT ID, 
         CASE ROW_NUMBER() OVER(PARTITION BY ID ORDER BY 1/0) % 2
           WHEN 1 THEN Value1
           WHEN 0 THEN -1 * Value1 END AS Value1,
         CASE ROW_NUMBER() OVER(PARTITION BY ID ORDER BY 1/0) % 2
           WHEN 1 THEN Value2
           WHEN 0 THEN -1 * Value2 END AS Value2
  FROM dbo.test22
  )
  SELECT ID, SUM(Value1) AS Value1, SUM(Value2) AS Value2
  FROM cte
  GROUP BY ID

Demo on SQLFiddle

Upvotes: 0

Fabio
Fabio

Reputation: 32445

This query will give a absolute difference between max value of ID and min value of same ID:

SELECT ID
, ABS(MAX(VALUE1) - MIN(VALUE1)) AS v1Diff
, ABS(MAX(VALUE2) - MIN(VALUE2)) AS v2Diff
FROM TABLE1
GROUP BY ID

Sql Fiidle

But if you want get a real difference(negative diff) then we need to know which row is first and which row is next. Then we can count difference like firstRowValue - nextRowValue.

Maybe your table has some RowID or DateTime column from where we can ordering a rows from same ID. What column/columns are Primery Key in your table?

Upvotes: 0

Praveen Nambiar
Praveen Nambiar

Reputation: 4892

You can try:

SELECT t1.ID, max(t2.VALUE1 - t1.VALUE1)
FROM TABLE1 t1
    left join TABLE1 t2 on t1.id = t2.id
group by t1.id

SQL FIDDLE DEMO:

Upvotes: 2

MarcinJuraszek
MarcinJuraszek

Reputation: 125610

You can use following:

SELECT
    ID,
    MAX(Value1) - MIN(Value1),
    MIN(Value2) - MAX(Value2)
FROM
    myTableName
GROUP BY
    ID

But there is one assumption: the second row has always greater Value1 and lower Value2 than first one.

Upvotes: 3

Related Questions