SitePoint Sponsor

User Tag List

Page 1 of 3 123 LastLast
Results 1 to 25 of 62

Hybrid View

  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    The sum of the recent m1Bs or m2Bs

    Code:
    data in myTable
    j  m1 m1Bs  m2  m2Bs
    1  2   100  1   -100 
    2  2   220  3   -120 
    3  4    40  1   -140 
    4  2   520  6   -300 
    5  1   -90  4    -10 
    6  5    60  4    -70
    I have data in myTable like the above.

    J represents Journal
    M represents Member
    Bs represents Balance


    The table has 6 members at the moment.
    1,2,3,4,5 and 6 in m1 column or m2 column.


    The st member "1" is in m2 column of the 1st record, m2 column of the 3rd record and m1 column of the 5th record.

    I am interested in the 5th record because it's the latest record.
    So the balance of the current state of the 1st member "1" is -90.

    For the 2nd member "2",
    the balance of the current state of the 2nd member "2" is 520.

    The balance of the current state of the 3rd member "3" is -120.

    The balance of the current state of the 4th member "4" is -70.

    The balance of the current state of the 5th member "5" is 60.

    The balance of the current state of the 6th member "6" is -300.

    I like to know the sum of all member's balance.

    It's 0 ;-90+520-120-70+60-300."



    I like to get the sum "0" with SQL.

    The following would-be coed doesn't work correctly, but I hope it shows what I want.
    Code:
    would-be code
    select sum(m1Bs, m2Bs) as the_sum_of_final_balance 
    from journal
    where group by m1 or m2
    
    target result
    
    0
    Actually the target result is always "0." because the table records give and take of each member's transaction. It's like zero sum game.

    If the member "2" give 100 dollars to the member "1" in the 1st record, the balance of the member 2 will be 100 dollars and the balance of the member 1 will be -100 dollars.

    But I need the target result 0 with SQL for data verification.
    That is if the target result is 0, it is saying the data is correct, if the target result is not 0, it is saying something wrong in the data of the table.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dotJoon View Post
    I am interested in the 5th record because it's the latest record.
    if you are sequencing your rows with an auto_increment number, you're doing it wrong

    add a date or datetime column to be sure of "latest" status
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    if you are sequencing your rows with an auto_increment number, you're doing it wrong
    Column j is auto_increment number. (I don't understand) why I am doing it wrong...)





    Quote Originally Posted by r937 View Post
    add a date or datetime column to be sure of "latest" status
    I added the dateTime column like the below with CFML "#dateformat(now(),'yyyymmdd')##timeformat(now(),'HHmm')#."

    Code:
    data in myTable
    j  m1 m1Bs  m2  m2Bs   dateTime
    1  2   100  1   -100   200911200640
    2  2   220  3   -120   200911220132
    3  4    40  1   -140   200911220317
    4  2  520   6   -300   200911241557
    5  1   -90  4    -10   200911241846
    6  5    60  4    -70   200911281930
    How can I verify whether the sum of the latest status in m1Bs and m2Bs is 0 in SQL with the data in myTable above?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    start by writing a query to get the latest time per member

    you've done this before
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    start by writing a query to get the latest time per member
    Should I use GROUP BY?

    Should I use max(dateTime)?


    Quote Originally Posted by r937 View Post
    you've done this before
    I have no Idea, although I search my former posts, I couldn't find it yet.

    Do you know the URL which I've done this before?

  6. #6
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    start by writing a query to get the latest time per member
    What about the following?
    Code:
    select max(dateTime) as maxDateTime
    from journal
    group by m1,m2
    The code above produces the result below.

    Code:
    200911241846
    200911200640
    200911220132
    200911241557
    200911220317
    200911281930
    What's wrong in the code above or what's next?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dotJoon View Post
    What's wrong in the code above or what's next?
    which member does 200911241557 belong to?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    which member does 200911241557 belong to?
    Two members belong to 200911241557.
    They are member 2 in m1 column and member 6 in m2 column.

    Now what is next?

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i don't think you understand what i was asking

    your query was supposed to return the latest time per member

    however, all it did was return some times

    my question was, if you retrieve a bunch of times like this --

    200911241846
    200911200640
    200911220132
    200911241557
    200911220317
    200911281930

    then how do you know which time belongs to which member?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    then how do you know which time belongs to which member?
    Do you mean the following code and its result?

    Code:
    code
    
    select max(dateTime) as maxDateTime, m1, m2
    from journal
    group by m1,m2
    
    result
    
    m1 m2 dateTime
    1  4  200911241846 
    2  1  200911200640 
    2  3  200911220132 
    2  6  200911241557 
    4  1  200911220317 
    5  4  200911281930

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    sorry, that is not the correct solution

    i wanted you to find the latest time for each member

    instead, you have provided only the latest time for each pair of members
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i wanted you to find the latest time for each member
    Yes, I want I to find the latest time for each member instead of each pair of members.

    How?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    how? with a GROUP BY on just the member column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    with a GROUP BY on just the member column
    There is no member column in myTable, but m1 column and m2 column.

    How can I express the member column which has m1 and m2 in SQL?

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    try a UNION query -- one SELECT to pull out m1, and another SELECT to pull out m2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    try a UNION query -- one SELECT to pull out m1, and another SELECT to pull out m2
    Do you mean the following?
    Code:
    code
    
    select m1, m2, max(dateTime) as maxDateTime
    from journal
    group by (m1)
    
    union
    
    select m1, m2, max(dateTime) as maxDateTime
    from journal
    group by (m2)
    
    result
    
    m1 m2 dataTime
    1  4  200911241846 
    2  1  200911241557 
    4  1  200911220317 
    5  4  200911281930 
    2  1  200911220317 
    2  3  200911220132 
    1  4  200911281930 
    2  6  200911241557

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    no, what i meant was, one SELECT to pull out m1, and another SELECT to pull out m2

    not one SELECT to pull out m1 and m2, and another SELECT to pull out m1 and m2 (not this)

    just one SELECT to pull out m1, and another SELECT to pull out m2 (just this)

    with times, of course
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Do you mean like the following although it is not correctly work?
    Code:
    select max(dateTime) as maxDateTime, union(m1, m2) as m
    from journal
    group by m1,m2

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    no, nothing like that at all

    let's go back even further

    can you write a query to get the latest time for each m1?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    can you write a query to get the latest time for each m1?
    No...
    I guess I know what you're saying.
    But I can't write it in SQL.

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dotJoon View Post
    But I can't write it in SQL.
    yes, you can

    just take this query you wrote earlier (which was wrong) --
    Code:
    select max(dateTime) as maxDateTime, m1, m2
    from journal
    group by m1,m2
    and remove m2 completely

    show the results that this query produces

    then change the query slightly, to produce a second query, to get the latest time for each m2

    show the results of the second query

    and then see if you can understand what's happening in each query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    select max(dateTime) as maxDateTime, m1
    from journal
    group by m1,m2

    (Sorry, I accidently modify this. so I retieved basic part again )

  23. #23
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dotJoon View Post
    Now I have the following code and its result.
    Code:
    code
    
    select max(dateTime) as maxDateTime, m1
    from journal
    group by m1,m2
    you are either (1) not listening, or (2) not paying attention

    i asked you to remove m2 completely, but you didn't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    what's next is to get the latest times for each m
    Code:
    SELECT m, MAX(maxDateTime) AS latest
      FROM ( put your UNION query here ) AS u
    GROUP BY m
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #25
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT m, MAX(maxDateTime) AS latest
      FROM ( put your UNION query here ) AS u
    GROUP BY m
    The code below produces an Error.

    Code:
    SELECT m, MAX(maxDateTime) AS latest
      FROM  m1 as m union m2 as m  AS u
    GROUP BY m
    I guess I never did UNION in FROM clause before.
    Where should I put myTable name "journal" inside red part?


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
  •