Menu
I have a SQL 2008 database table like this
name score ----------------------- steve 207 steve 205 steve 200 steve 139 I want to get the difference between the rows. eqn = [row - (row + 1)] so I would ideally want it to be,
steve 2 //207 - 205 steve 5 //205 - 200 steve 61 //200 - 139 steve 139 //139 - 0 What is the best way to do this? Thanks!
973
In the blue text, you can see the calculation of the SQL delta between two rows. To calculate a difference, you need a pair of records; those two records are “the current record” and “the previous year's record”. You obtain this record using the LAG() window function.
Find Row Differences in ExcelClick “Find & Select” and pick “Go To Special” in the drop-down list. In the window that pops open, choose “Row Differences” and click “OK.” The window will automatically close, and you'll see the differences in your rows highlighted.
Here's the SQL query to compare each row with previous row. In the above query, we join sales table with itself using an INNER JOIN condition g2.id=g1.id + 1 that allows you to compare each row with its previous row. Please note, this condition depends on the fact that our id column has consecutive numbers.
The SQL MINUS operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement. Each SELECT statement will define a dataset. The MINUS operator will retrieve all records from the first dataset and then remove from the results all records from the second dataset.
This is one way to do it
with cte as (SELECT ROW_NUMBER() OVER (PARTITION BY table.name ORDER BY id) row, name, score FROM table) SELECT a.name , a.score - ISNULL(b.score,0) FROM cte a LEFT JOIN cte b on a.name = b.name and a.row = b.row+1 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
