Results 1 to 7 of 7

Thread: Adding and subtract within a loop

  1. #1
    Join Date
    Aug 2005
    Posts
    518
    Rep Power
    0

    Default Adding and subtract within a loop

    i want to pull data from a mysql database and i want to calculate the difference between two set of data from the same field, just from different loops.

    example.

    Database:

    id Name Age
    1 tom 10
    2 mary 5
    3 jill 35

    i want to pull this data out "not by individual id" and get the difference between there ages... so difference between tom and mary is 5yrs

    i have no idea how to do this... so help guys
    Why fight Information Technology when you can outsource IT

  2. #2
    Join Date
    Dec 2002
    Posts
    500
    Rep Power
    0

    Default

    I assume the Table name Person

    Code:
    select	First.Name as FirstPerson, 
    	Second.Name as SecondPerson, 
    	(First.Age - Second.Age) as AgeDifference  
    from	Person as First, 
    	Person as Second
    where	First.Id < Second.Id
    i made it so u don t get jim | mary | 5 and mary | jim | -5 ... if u want that, use <> instead of <

    use this query if u hate to see the negative age differences
    Code:
    select	First.Name as FirstPerson, 
    	Second.Name as SecondPerson, 
    	(First.Age - Second.Age) as AgeDifference  
    from	Person as First, 
    	Person as Second
    where	First.Age > Second.Age Or (First.Age = Second.Age AND First.Id < Second.Id)
    Last edited by icymint3; Feb 22, 2007 at 11:13 AM. Reason: clarification
    Cultured in Aggression and Koding like a Warrior!!
    “Common sense is instinct. Enough of it is genius.” - George Bernard Shaw.
    "The significant problems we face cannot be solved by the same level of thinking that created them." - Albert Einstein

  3. #3
    Join Date
    Aug 2005
    Posts
    518
    Rep Power
    0

    Default

    in a loop with several name being retrieve from the data base will this assignment of first and second.. work.. if i have more name in my database
    Why fight Information Technology when you can outsource IT

  4. #4
    Join Date
    Oct 2005
    Posts
    745
    Rep Power
    0

    Default

    That query would return rows of the form

    FirstName, SecondName, Agedifference

    for all pairs of persons in the database...

    Query Result
    Code:
    
    FirstName SecondName AgeDifference
    Tom         Mary           5
    Tom         Jill              -25
    Mary        Jill              -30
    Last edited by recursion; Feb 22, 2007 at 10:42 AM.
    3.14159265358979323846264338327950288
    4197169399375105820974944592307816406
    28620899862803482534211706798 pi 101

  5. #5
    Join Date
    Aug 2005
    Posts
    518
    Rep Power
    0

    Default

    can u guys point me to where i can... get detail reading on this, pulling data in pairs.. and stuff.. cus i cant get .. what u guy have here to work...

    and yes i corrected the database... but the term 'first' is getting me confuse.. in that First.Name.. isnt it referring to a table name First in a database call Name..
    Why fight Information Technology when you can outsource IT

  6. #6
    Join Date
    Oct 2005
    Posts
    745
    Rep Power
    0

    Default

    First is used as an alias for the table Person. So you're basically pulling two instances of the same table and then doing comparisons on the records. Using the < or <> ensures you're not comparing a record with itself.
    Last edited by recursion; Mar 1, 2007 at 05:28 PM.
    3.14159265358979323846264338327950288
    4197169399375105820974944592307816406
    28620899862803482534211706798 pi 101

  7. #7
    Join Date
    Sep 2004
    Posts
    356
    Rep Power
    0

    Default

    i Send thee Novice to SQLZOO.NET thats the bible of SQL have fun will post later on how to solve your issue (yes going there now to get the solution first )

Posting Permissions

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