Greetings!
I need a little help with this statement, please. I have two tables: members, and payments, which book incoming payments and fees as positive and negative, (stored in 'amount'), and I wan't to list the difference-value for each member. This works so far with:
select m.id_memb, m.name, sum( p.amount)
from tbl_payments p, tbl_members m
where p.id_memb = m.id_memb
group by m.id_memb, m.name
But I would like to have an aditional row, which displays the last day of a payment, that is amount>0, next to each entry, so that I have this output:
id_memb | name: | amount due: | last day of receipt:
---------------------------------------------------------------
1 | paul | 1.50 | 2/4/12
2 | karl | 55.50 | 3/5/12
...
I tried:
select m.id_memb, m.name, -sum( b.amount) , last_rec
from (
select max(datum) as last_rec, id_memb as id_m
from tbl_payments
where amount>0
group by id_memb
),
tbl_payments b, tbl_members m
where b.id_memb= m.id_memb
and id_m = m.id_memb
and sum(b.amount)< 0
group by m.id_memb, m.name, last_rec
...but something is wrong with the group by statement. Is there a way I can fix this?
Thanks a lot,
tobi
Edited by: tpetri on 07.07.2012 11:34