how retrieve sum of multiple column ? codeigniter

Question

i have a table sales_items ,where we save all sales item with all details -my table is :

sales_id  | sales item| No.ofpacks |   quantity(liters pr pack) | 
---------     ----------- ------------   ---------------- 
   31           petrol        2               2.5
   31           disel de2     3               3.2
   34           petrol se     2               4.2
   31           castrol       7               4.1
------------------------------------------------------------------

now we want total number of liters at any one sales_id , means if we take sales_id 31 , then we want total liters =(2*2.5) + (3*3.2) +(7*7.2), i search but not found any suitable help on google . we try :

public function total_liter($id) {
        $q = $this->db->get_where('sale_items', array('sale_id' => $id), 1);
        if ($q->num_rows() > 0) {
            foreach($q as $row):

            $total_liter += $row['no_of_packs']*$row['quantity'];
            endforeach;
        }
        return FALSE;
    }

Is the method you wrote not giving the required result? The logic seems to be fine — nitigyan
– nitigyan, Commented Jul 1, 2015 at 13:03

Viral · Accepted Answer · 2015-07-01 13:21:11Z

1

I would suggest doing this using mysql

SELECT SUM(quantity * no_of_packs) as total FROM table_name WHERE sales_id = 31

Here is CI flavor,

$this->db->select('SUM(quantity * no_of_packs) as total', false);
$this->db->from('table_name');
$this->db->where('sales_id', 31);

Note that second argument in select will prevent CI from adding unnecessary backticks.

edited Jul 1, 2015 at 13:21

answered Jul 1, 2015 at 13:02

Viral

3,7531 gold badge20 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user3261307 Over a year ago

but how can we assign it's value in any variable , by which we can save in database means can we use , $total=SELECT SUM(quantity * no_of_packs) as total FROM table_name WHERE sales_id = 31 ;

CodeGodie Over a year ago

what does IMHO stand for?

Viral Over a year ago

by using variable instead 31, like "sales_id = $id" or $this->db->where('sales_id', $id); in CI where $id should be defined before query

CodeGodie Over a year ago

People need to stop using stupid acronyms, no offense to you. Or should I say NOTY?

Viral Over a year ago

how about now ? :) @CodeGodie

Collectives™ on Stack Overflow

how retrieve sum of multiple column ? codeigniter

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related