how to get sum of a column with codeigniter query

Question

I am trying to get sum of a column values in mysql table with the codeigniter query as

$this->db
     ->query("Select SUM(tb1.amount) from table1 tb1 inner join table2 tb2 on tb2.Id=tb1.Id Where tb2.Status='1'")
     ->result()

it give me error as array to string conversion I need just a number such count(amount) returns number of rows with num_row()

Answer 1

You can use select_sum() function of codeigniter. Try the following code-

$query = $this->db->select_sum("tb1.amount")
                  ->from("table1 as tb1")
                  ->join("table2 as tb2","tb1.id = tb2.id") 
                  ->where("tb2.status",1)
                  ->get();
$query = $query->result();

OR

$query = $this->db->query('SELECT sum(tb1.amount) FROM table1 as tb1 join table2 as tb2 on tb1.id = tb2.id where tb1.status = 1'); $query = $query->result_array();

Answer 2

use as => $data['total']

$data = $this->db
     ->query("Select SUM(tb1.amount) as total from table1 tb1 inner join table2 tb2 on tb2.Id=tb1.Id Where tb2.Status='1'")
     ->row_array()

Answer 3

Use row() instead of result if you want to get a single row.

$data = $this->db
     ->query("Select SUM(tb1.amount) as total from table1 tb1 inner join table2 tb2 on tb2.Id=tb1.Id Where tb2.Status='1'")
     ->row()

To get the data.

echo $data->total;