How to use Subqueries In SQL
Another approach for computing the delta between two columns on different rows is, by using subqueries to retrieve the value of the previous row and then calculate the value of the difference or delta.
Use the below command to compute the delta between two columns on different rows using subqueries.
SELECT
A.geek_value,
A.geek_value - (SELECT B.geek_value FROM geeks_table B WHERE B.geek_id + 1 = A.geek_id ) AS Delta
FROM
geeks_table A;
Output:
|
geek_value |
Delta |
|---|---|
|
100 |
NULL |
|
200 |
100 |
|
300 |
100 |
|
400 |
100 |
|
500 |
100 |
Explanation:
- (SELECT B.geek_value FROM geeks_table B WHERE B.geek_id + 1 = A.geek_id ): This kind of subqueries is called correlated subqueries. For every row value (A.geek_value) of geeks_table(A), this subquery returns the previous row value(B.geek_value) of geeks_table(B) by using the subsequent geek_id of the geeks_table.
- SELECT A.geek_value, A.geek_value – (SELECT B.geek_value FROM geeks_table B WHERE B.geek_id + 1 = A.geek_id ) AS Delta: This part of the query computes the difference between the first occurrence of geeks_table(A) geek_value column and value returned by subquery as aliases it as Delta.
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,