sum and groupby

[xq]

i have to sum up most recent record which comments is 'repay principal' and group by deal_id, the following sql is definitly not working, it will sum up all the record include those even not happen yet, after current date. how to do it?

select events.deals_id, sum(cflows_amount)
from events, cflows
group by deals_id

and this one also is not right, it has to be grouped by deals_id.

select events.deal_id, sum(cflows_amount)
from events, cflows
where cflows.date<= CURRENT DATE, cflows.command = 'REPAY PRINCIPAL'

how do i conbine these two together?
hope u could understand my question.
thanks in advance for any help!

 

[pipk]

I think if you are trying to retrieve from 2 tables you will need to have a join somewhere in there so that a record from from table can be associated with a record from the other.

Is there a key field that can be used for the join?

 

[xq]

sorry my mistake, i just simplified the problem that
i don't have to retrieve from 2 tables, what if i just do like that: (neither of them is right)

select cflows.deals_id, sum(cflows.amount)
from cflows
group by cflow.deals_id

or

select cflows.deal_id, sum(cflows.amount)
from cflows
where cflows.date<= CURRENT DATE, cflows.command = 'REPAY PRINCIPAL'

i know i cann't follow WHERE after GROUP BY, what else i could do instead of them?
thank u very much!

 

[Guest_imported]

... positive, but you can have a GROUP BY after a WHERE clause, can't you.

So something like

select cflows.deal_id, sum(cflows.amount)
from cflows
where cflows.date<= CURRENT DATE
and cflows.command = 'REPAY PRINCIPAL'
group by cflows.deal_id

should give you the desired result. Not ?

milan432

 

[angiole]

Are you looking for perhaps, the latest cflows_amount for each deals_id record with 'Repay Principal' in the comments?

 

[xq]

to Millan432, thanks but it doesn't work, syntax error with that.
to angiole, i'm not only looking for the latest, i have to sum up all the record till the latest,do u have any idea?

 

[xq]

sorry!! it does work as millan said, thank u all!