sql - How to combine to values from two records -

hi guys have following code should @ event cost, figure out transaction table if been paid , display unpaid balance.

select casestudy_client.cleint_fname first_name,          casestudy_client.client_sname surname,         casestudy_client.client_phonenumber phone_number,         casestudy_event.event_totalcost- casestudy_transaction.transaction_value unpaid_balance   casestudy_client   inner join casestudy_event     on casestudy_client.client_id = casestudy_event.event_clientid   inner join casestudy_transaction     on casestudy_event.event_id = casestudy_transaction.transaction_eventid   casestudy_event.event_eventstage ='complete' ,          casestudy_event.event_totalcost > casestudy_transaction.transaction_value; 

this works if have multi part transaction fails pick matching event id , assumes event.

in test data have event costs £500, 1 transaction has value of 400 , 1 transaction value of 99. current output shows 2 records 1 unpaid balance of 100 , other 401. show 99

if possible in relatively simple way please let me know can't figure out how make work

you need add transactions.

select casestudy_client.cleint_fname first_name,          casestudy_client.client_sname surname,         casestudy_client.client_phonenumber phone_number,         casestudy_event.event_totalcost- sum(casestudy_transaction.transaction_value) unpaid_balance   casestudy_client   inner join casestudy_event     on casestudy_client.client_id = casestudy_event.event_clientid   inner join casestudy_transaction     on casestudy_event.event_id = casestudy_transaction.transaction_eventid   casestudy_event.event_eventstage ='complete' ,          casestudy_event.event_totalcost > casestudy_transaction.transaction_value group casestudy_client.cleint_fname, casestudy_client.client_sname, casestudy_client.client_phonenumber, casestudy_event.event_totalcost 

also, cleint_fname typo.