MySQL - math operations between rows [duplicate]

Question

I have a MySQL 8 database table like this:

+-------+
| Total |
+-------+
| 100   |
+-------+
| 40    |
+-------+

I want to get the difference between the rows -> result = (row - (row + 1)) Before the math operation I always have only two rows.

My desired result is something like this:

+--------+
| Result |
+--------+
| 60     |
+--------+

What is the best way to do this?

I came up with something like this:

SELECT ABS( (a.total - LEAD(a.total,1,0) OVER(ORDER BY t.total) )) AS result FROM (...) a LIMIT 1

Before the math operation I always have only two rows.

for that you can use window functions, but to use them you need a column that defines the order — nbk
– nbk, Commented Jan 30, 2024 at 17:35
See mysqltutorial.org/mysql-window-functions/mysql-lag-function or similar. — PM 77-1
– PM 77-1, Commented Jan 30, 2024 at 17:39
what do you want if there are no rows, or one row, or more than two rows? — ysth
– ysth, Commented Jan 30, 2024 at 18:20
What's missing right now is a way to define the order for the rows. That is, which row relative to a given row, exactly, is "row + 1". Remember, databases do not have any concept and explicitly disclaim all responsibility for knowing any kind of natural order or insert order. In fact, they can even move rows around on disk or return different results for the same query from run to run if you don't define the order within the confines of the data itself. — Joel Coehoorn
– Joel Coehoorn, Commented Jan 30, 2024 at 20:36

Pércoles Tiago Napivoski · Accepted Answer · 2024-01-30 17:37:01Z

0

Maybe Window Functions. I particularly never used but it seems to do the work.

answered Jan 30, 2024 at 17:37

1887 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1