# The sum of the recent m1Bs or m2Bs

Show 200 post(s) from this thread on one page
Page 1 of 3 123 Last
• Dec 3, 2009, 07:37
dotJoon
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.
• Dec 3, 2009, 09:18
r937
Quote:

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
• Dec 3, 2009, 15:21
dotJoon
Quote:

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...)

Quote:

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?
• Dec 3, 2009, 17:46
r937
start by writing a query to get the latest time per member

you've done this before
• Dec 3, 2009, 18:09
dotJoon
Quote:

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)?

Quote:

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?
• Dec 3, 2009, 19:34
dotJoon
Quote:

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?
• Dec 3, 2009, 21:44
r937
Quote:

Originally Posted by dotJoon
What's wrong in the code above or what's next?

which member does 200911241557 belong to?
• Dec 4, 2009, 00:06
dotJoon
Quote:

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?
• Dec 4, 2009, 05:26
r937
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?
• Dec 4, 2009, 05:43
dotJoon
Quote:

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```
• Dec 4, 2009, 06:09
r937
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
• Dec 4, 2009, 06:18
dotJoon
Quote:

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?
• Dec 4, 2009, 06:25
r937
how? with a GROUP BY on just the member column
• Dec 4, 2009, 06:33
dotJoon
Quote:

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?
• Dec 4, 2009, 06:57
r937
try a UNION query -- one SELECT to pull out m1, and another SELECT to pull out m2
• Dec 4, 2009, 09:40
dotJoon
Quote:

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```
• Dec 4, 2009, 09:46
r937
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
• Dec 4, 2009, 09:51
dotJoon
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```
• Dec 4, 2009, 09:57
r937
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?
• Dec 4, 2009, 10:04
dotJoon
Quote:

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.
• Dec 4, 2009, 10:57
r937
Quote:

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
• Dec 4, 2009, 11:19
dotJoon
Code:

```select max(dateTime) as maxDateTime, m1 from journal group by m1,m2```

(Sorry, I accidently modify this. so I retieved basic part again )
• Dec 4, 2009, 11:20
r937
Quote:

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
• Dec 4, 2009, 11:30
dotJoon
Do you mean this?
Code:

```code select m1 as m, max(dateTime) as maxDateTime from journal group by m1 union select m2 as m, max(dateTime) as maxDateTime from journal group by m2 result 1 200911241846 2 200911241557 4 200911220317 5 200911281930 1 200911220317 3 200911220132 4 200911281930 6 200911241557```
If the code above is near to what you suggested, what's next?
• Dec 4, 2009, 11:45
r937
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```
Show 200 post(s) from this thread on one page
Page 1 of 3 123 Last