# Thread: The sum of the recent m1Bs or m2Bs

1. ## 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. Originally Posted by dotJoon
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

3. Originally Posted by r937
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...)

Originally Posted by r937
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. start by writing a query to get the latest time per member

you've done this before

5. Originally Posted by r937
start by writing a query to get the latest time per member
Should I use GROUP BY?

Should I use max(dateTime)?

Originally Posted by r937
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. Originally Posted by r937
start by writing a query to get the latest time per member
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. Originally Posted by dotJoon
What's wrong in the code above or what's next?
which member does 200911241557 belong to?

8. Originally Posted by r937
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. 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?

10. Originally Posted by r937
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. 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

12. Originally Posted by r937
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. how? with a GROUP BY on just the member column

14. Originally Posted by r937
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. try a UNION query -- one SELECT to pull out m1, and another SELECT to pull out m2

16. Originally Posted by r937
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. 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

18. 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. 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?

20. Originally Posted by r937
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. Originally Posted by dotJoon
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

22. 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. Originally Posted by dotJoon
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

24. 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```

25. Originally Posted by r937
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?

#### Posting Permissions

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