Calculating difference between rows MySQL

226
July 05, 2017, at 00:06 AM

I have a table like this:

+----------+-----+--------+----+---------+-----+--------+ 
| Week     | #X  | Diff_X | #Y | Diff_Y  | #Z  | Diff_Z |
+----------+-----+--------+----+---------+-----+--------+ 
| 22       | 25  | Null   | 13 | NULL    |  0  | NULL   |
| 23       | 25  | Null   | 18 | NULL    |  12 | NULL   |
| 24       | 39  | Null   | 15 | NULL    |  16 | NULL   |
+----------+-----+--------+----+---------+-----+--------+ 

This updates every week, so 1 row a week.

How would I proceed into calculating the difference between each value between each week? and put them in the designated column.

So the desired result should look like this:

+----------+-----+--------+----+---------+-----+--------+ 
| Week     | #X  | Diff_X | #Y | Diff_Y  | #Z  | Diff_Z |
+----------+-----+--------+----+---------+-----+--------+ 
| 22       | 25  | 0      | 13 | 0       |  2  | 0      |
| 23       | 25  | 0      | 18 | 5       |  12 | 10     |
| 24       | 39  | 14     | 15 | -3      |  16 | 4      |
+----------+-----+--------+----+---------+-----+--------+ 

I am totally clueless in what to do, so any kind of help would be much appreciated!

Answer 1
UPDATE mytable t1
INNER JOIN mytable t2 ON t1.Week + 1 = t2.Week
SET t2.`Diff_X` = t2.`#X` - t1.`#X`,
    t2.`Diff_Y` = t2.`#Y` - t1.`#Y`,
    t2.`Diff_Z` = t2.`#Z` - t1.`#Z`
READ ALSO
PHP do-while loop ending with exhausted memory error when executing sql code

PHP do-while loop ending with exhausted memory error when executing sql code

Whilst executing the following code, the site crashes with:

153
PHP do-while loop ending with exhausted memory error when executing sql code

PHP do-while loop ending with exhausted memory error when executing sql code

Whilst executing the following code, the site crashes with:

20
PHP do-while loop ending with exhausted memory error when executing sql code

PHP do-while loop ending with exhausted memory error when executing sql code

Whilst executing the following code, the site crashes with:

165
php search the value

php search the value

Hello every one i am new to php i need some help in search the exact value of school student roll numbers

117