SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Substract problem

    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.

    Code:
     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.

    Code:
    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.

  2. #2
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Solved.

    Could be closed.

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    82
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 :

    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •