Computing Difference (Delta) Between Two Columns on Different Rows
In MySQL, computing the difference between two columns on different rows can be done in multiple ways,
- Using Window Functions
- Using SELF JOIN
- Using Subqueries
- Using Common Table Expressions (CTEs)
First, we’ll create a table named geeks_table with two columns geek_id, and geek_value.
CREATE TABLE geeks_table(
geek_id SERIAL PRIMARY KEY,
geek_value INT
);
Now we’ll insert values 100,200,300,400,500 into the table
INSERT INTO geeks_table(geek_value) VALUES (100),(200),(300),(400),(500);
After execution of the above commands, geeks_table is ready with some data which is helpful for further computations. Now our table looks like,
|
geek_id |
geek_value |
|---|---|
|
1 |
100 |
|
2 |
200 |
|
3 |
300 |
|
4 |
400 |
|
5 |
500 |
Now here are some examples of computing the difference or delta between ‘geek_value‘ columns of different rows using ‘geeks_table‘ which we have created above.
Compute a Difference (Delta) Between Two Columns on Different Rows
MySQL is an open-source, Relational Database Management System that stores data in a structured format using rows and columns. It’s software that enables users to create, manage, and manipulate databases. Similar to SQL we use queries to store and access data in the MySQL databases.
In this article, we’ll learn how to calculate the difference (delta) between two columns on different rows. Before moving into this article, you need to install MySQL. To do so you can refer to the below articles,