Counting a values in a column

[eluser]codemonkey[/eluser]
Hello, I have this piece of code that I need to modify to instead of counting the number of matching rows, sum up the values in the columns of the matching rows and output that value whilst also outputting the unique rows to build a table of unique rows with the totals.

So if I have a db table containing:

NAME | Amount
balls | 10
cups | 5
balls | 2
balls | 1
cups | 2

I want to build a table (using CI table) containing two rows and two columns

NAME | Amount
balls | 13
cups | 7

Code:

//grab rows for peripherals
        $this->db->select('make')->select('model, COUNT(id) AS total')->group_by('model')
                    ->where('item_type =', "per")
                    ->where('action_type', "checkin")
                    ->or_where('action_type', "stockin");
        $stockrows = $this->db->get('items')->result_array();

        foreach ($stockrows as $count => $stockrow)
        {
            $stockrows[$count]['model'] = $stockrow['model'];
        }
        
        $data['stockrecords_per'] = $stockrows;

of course the code does not match the example, it was just easier explaining using cups and balls.

Thanks

[eluser]gRoberts[/eluser]
Try using "sum" instead.

i.e.

Code:

$this->db->select('make')->select('model, SUM(Amount) AS total')->group_by('model')

You would need to change `Amount` to the column that contains the `Amount` value in your example above but what your doing at the moment is "Counting" how many rows there are, rather than adding each of the values together.

`Sum` takes the value of each of the rows caught in the "group_by" and adds them together.

[eluser]Chathuranga Tennakoon[/eluser]
you can do this without active query as well.

Code:

$this->db->query('select model, sum(Amount) as Total from make group by model');