Substract problem

[nydiann]

Hello.

I am not experenced much in MySQL , so...

I Have a problem with substract function in MySQL , hope I get help here.

Table with data :

Name Salary Date

Matias 2000$ 2010-03-24
Tomas 5350$ 2010-03-24
Ricardo 3970$ 2010-03-24
Matias 1259$ 2010-03-25
Tomas 3450$ 2010-03-25
Ricardo 1920$ 2010-03-25
Matias 8345$ 2010-03-26
Tomas 2345$ 2010-03-26
Ricardo 2399$ 2010-03-26

Now I am getting data by SELECT , a desired name.

 SELECT * FROM database WHERE Name LIKE 'Ricardo'

Name Salary date

Matias 2000$ 2010-03-24
Matias 1259$ 2010-03-25
Matias 8345$ 2010-03-26


If we add one more column

ID Name Salary Date

1 Matias 2000$ 2010-03-24
2 Tomas 5350$ 2010-03-24
3 Ricardo 3970$ 2010-03-24
4 Matias 1259$ 2010-03-25
5 Tomas 3450$ 2010-03-25
6 Ricardo 1920$ 2010-03-25
7 Matias 8345$ 2010-03-26
8 Tomas 2345$ 2010-03-26
9 Ricardo 2399$ 2010-03-26


We can count a difference between Salary of ID 1 and Salary of ID 2 , but it's kinda useless for me cause I want to get Salary for each person separately.

SELECT *, coalesce( a.salary - (

SELECT b.salary
FROM taxes b
WHERE b.id = a.id+1
), 0 ) AS diff
FROM taxes a
WHERE name LIKE 'Matias'
ORDER BY date

It's showing up as :

something like it...

+------+------------------------+
| id |length | difference |
+------+------------------------+
| 1 | 1090 | 202 |
| 2 | 888 | 343 |
| 3 | 545 | 111 |
| 4 | 434 | 389 |
| 5 | 45 | 45 |

Anyone knows how to get something similar? but for desired person?

Instead of ID , could be used a date.

probably it's a small edit of code , but I really do not know how do it.



Any suggestions , will be apreciated.

[nydiann]

Solved.

Could be closed.

[nydiann]

Updated.

Got some other problems.

I don't know how could I show up a statistics like that:


for example :

Highscores of a game:

Name of player Experience (gained yesterday)

Matias 6 000

Ricardo 4 000

Tomas 2 000


I know how can I show it up , but only for each person separately .It can be done by following code :

SELECT *  , coalesce( a.exp - (
SELECT b.exp
FROM table_name b
WHERE DATE_ADD( b.date, INTERVAL 1
DAY ) = a.date
AND name LIKE 'name_of_player' ) , 0 ) AS diff
FROM World a
WHERE name LIKE 'name_of_player'' AND date LIKE (SELECT MAX(date) from World )
ORDER BY date

Any solution how could I do that for all of them?

PHP code also acceptable.

Thanks for any kind of assistance.