CODEWITHSUNDEEP
codeignitersumgroup
cleo
cleo

Reputation: 17

CodeIgniter Group with SUM

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

Upvotes: 0

Views: 588

Answers (3)

KUMAR
KUMAR

Reputation: 1995

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

Upvotes: 0

Vickel
Vickel

Reputation: 7997

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

Upvotes: 0

CaPs LoCk
CaPs LoCk

Reputation: 150

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);

Upvotes: 2

Related Questions