How to get balance of cols

[eluser]aryan_[/eluser]
I have table which contains debited and credited amount. Every time user make a transaction, a new row is inserted. e.g.

Code:

------------------------------------
id  |  user_id  |  credit |  debit
------------------------------------
1   |   4       |   20.00 |  5.00  
------------------------------------
2   |   6       |  100.00 |  25.00  
------------------------------------
3   |   4       |    0.00 |  5.00  
------------------------------------

Now the balance for user_id 4 would be (20.00 + 0.00) - (5.00 + 5.00) = 10.00

How to do this using active record?

[eluser]jcavard[/eluser]
I guess this should do the trick [untested]

Code:

$this->db->select('(SUM(credit) - SUM(debit)) as balance');
$this->db->from('transaction');
$this->db->group_by('user_id');

[eluser]aryan_[/eluser]
[quote author="jcavard" date="1249952629"]I guess this should do the trick [untested]

Code:

$this->db->select('(SUM(credit) - SUM(debit)) as balance');
$this->db->from('transaction');
$this->db->group_by('user_id');

[/quote]

Thanks a lot! How will it return the value?

[eluser]jcavard[/eluser]
we're talking basic active record here, you should have the user guide open handy all the time. Anyway
this could be your function in the transaction model

Code:

function get_user_balance()
{
  $this->db->select('user_id');
  $this->db->select('(SUM(credit) - SUM(debit)) as balance');
  $this->db->from('transaction');
  $this->db->group_by('user_id');
  $handle = $this->db->get();
}

you would call it from your controller this way

Code:

function account()
{
  $balances = $this->your_model_name->get_user_balance();
  foreach($balances->result() as $row)
  {
    echo "Balance for user id " . $row->user_id . " is " . sprintf(".2f$", $row->balance) . "$<br />";
  }
}

will output this fancy text:

Code:

Balance for user id 4 is 10.00 $
Balance for user id 6 is 75.00 $