+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Need Help for sum statement (/showthread.php?tid=50466)
Need Help for sum statement - El Forum - 03-27-2012
[eluser]Farzand[/eluser]
i m trying to sum two tables field.
but on view i get double record. my db table have 3 record and i to sum them but when i m going to view i get 6 record.
some one help to sum two table field?
$this->db->select("bt_tayyab_lc.id as id, sum(bt_tayyab_lc.weight) as weight,bt_tayyab_remarks.id as id, bt_tayyab_remarks.lcid as lcid");
$this->db->join('tayyab_remarks','tayyab_lc.id = tayyab_remarks.lcid' ,'left');
$this->db->where('tayyab_lc.creat_at >=', $currentdate1);
$this->db->where('tayyab_lc.creat_at <=', $currentdate);
$this->db->where('tayyab_lc.cid','1');
$data['supplier_account'] = $this->db->get('tayyab_lc');
Need Help for sum statement - El Forum - 03-27-2012
[eluser]Farzand[/eluser]
$this->db->select(“bt_tayyab_lc.id as id, sum(bt_tayyab_lc.weight) as weight,bt_tayyab_remarks.id as id, bt_tayyab_remarks.lcid as lcid”);
$this->db->join(‘tayyab_remarks’,‘tayyab_lc.id = tayyab_remarks.lcid’ ,‘left’);
$this->db->where(‘tayyab_lc.creat_at >=’, $currentdate1);
$this->db->where(‘tayyab_lc.creat_at <=’, $currentdate);
$this->db->where(‘tayyab_lc.cid’,‘1’);
$data[‘supplier_account’] = $this->db->get(‘tayyab_lc’);
Need Help for sum statement - El Forum - 03-27-2012
[eluser]Matalina[/eluser]
First you can't alias two fields with the same name
Second You need to use a group_by statement if you are summing up things and selecting non sums
Code:
$this->db->select(“bt_tayyab_lc.id as id1, sum(bt_tayyab_lc.weight) as weight,bt_tayyab_remarks.id as id2, bt_tayyab_remarks.lcid as lcid”);
$this->db->join(‘tayyab_remarks’,‘tayyab_lc.id = tayyab_remarks.lcid’ ,‘left’);
$this->db->where(‘tayyab_lc.creat_at >=’, $currentdate1);
$this->db->where(‘tayyab_lc.creat_at <=’, $currentdate);
$this->db->where(‘tayyab_lc.cid’,‘1’);
$this->db->group_by(bt_tayyab_lc.id, bt_tayyab_remarks.id,bt_tayyab_remarks.lcid); // <----- here
$data[‘supplier_account’] = $this->db->get(‘tayyab_lc’);Need Help for sum statement - El Forum - 03-27-2012
[eluser]Farzand[/eluser]
Thanks alot dear i got my mistake so can tell more one thing i want sum 2 field of both tables so should i need to do?
$this->db->select("bt_tayyab_lc.id as id1, sum(bt_tayyab_lc.weight) as weight,
bt_tayyab_remarks.id as id2,
sum(bt_tayyab_remarks.remarks) as remarks,
bt_tayyab_remarks.lcid as lcid");
$this->db->join('tayyab_remarks','tayyab_lc.id = tayyab_remarks.lcid');
$this->db->where('tayyab_lc.creat_at >=', $currentdate1);
$this->db->where('tayyab_lc.creat_at <=', $currentdate);
$this->db->where('tayyab_lc.cid','1');
$this->db->group_by('bt_tayyab_lc.id', 'bt_tayyab_remarks.id','bt_tayyab_remarks.lcid'); // <----- here
$data['supplier_account'] = $this->db->get('tayyab_lc');
Need Help for sum statement - El Forum - 03-27-2012
[eluser]Matalina[/eluser]
You results are going to depends on how your database is set up. I wouldn't suggest summing two fields from two different tables in one query like that.
Can you explain what you are trying to do? I think I understand, but I want to make sure before I give you code to help. Basically it's going to come down to a subquery inside of the select statement. But it may not be what you want either.
Need Help for sum statement - El Forum - 03-27-2012
[eluser]Farzand[/eluser]
ok i explain you.
1:- i trying to make reports between to dates
2:- first table is tayyab_lc and his sub table is tayyab_remarks. when i search between dates so first i search in main table and sum weight then get his id and find sub tables and sum remarks. but sub table don`t search between dates.
bt_tayyab_lc table have these fields id,weight,creat_at
bt_tayyab_remarks have these fields id,lcid,remarks,creat_at
i want make relation between these fields but with sum
first search this
bt_tayyab_lc sum(weight) between two dates
then do this
bt_tayyab_lc. id bt_tayyab_remarks. lcid
sum(bt_tayyab_remarks. remarks)
i hope you understand becoz i am new in php forums so i use codeintor to build this webbase lc managment system