I want to learn CodeIgniter, with an example case like this:

I have a database (tbl_points)

And I have an output like this:

id	sku	point
1	001	10
2	001	-1
3	002	5
4	002	-2
5	001	-1

I'm having a hard time making groups based on SKUs and then adding up each value at that point. Can you help me to make it like this? Thank you very much.

id	sku	point
1	001	8
2	002	3

CodePudding user response：

If you want to write query for ci3.

$this->db->select('sku');
$this->db->select_sum('point');
$this->db->from('tbl_points');
$this->db->group_by('sku');
$query = $this->db->get(); 
$row = $query->result();
          
print_r($row);

CodePudding user response：

This is resolved with a simple mysql SUM(), see Aggregate Function Descriptions

select * , sum(`point`) as my_point
from `tbl_points`
group by `sku`

in case you really need a new "id", you can extend your query using a User-Defined Variable to add on 1 for each new row:

set @row_num=0;
select * , sum(`point`) as my_point, @row_num:=@row_num 1 AS new_id
from `tbl_points`
group by `sku`

check the corresponding mysql fiddle

create that query with Codeigniter Query Builder example for CI3.x:

$this->db->select('* , sum(point) as my_point');
$this->db->group_by('sku');
$query = $this->db->get('tbl_points');

and with the User-Defined Variable you need 2 queries, something like:

$sql="set @row_num:=0";
$this->db->query($sql);

$sql="select * , sum(`point`) as my_point, @row_num:=@row_num 1 AS new_id
from `tbl_points`
group by `sku`
";

$query=$this->db->query($sql);

to quickly output the result, you could write this line:

echo '<pre>';print_r($query->result());

anyway recommendable to check How to Generate CI Query Results

CodePudding user response：

You need to sum it first, then group by this fields :-

$sql = "select id,sku, sum(`point`) as my_point from `tbl_points`
group by `sku`";

$query = $this->db->query($sql);

to output the result:-

echo '<pre>';
print_r($query->result());

Your Output result :-

id  sku my_point
1   001 8
3   002 3