Need Help for sum statement

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

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

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

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

[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.

[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