Getting Sum of db field using join active record in codeigniter

Question

I have a table name products with all product details and another whs_products with quantity details of the products for each warehouse.

i want select id, code and name from products table and sum of quantity where products.id = whs_products.product_id

I am trying this

$this->db->select("id, code, name");
$this->db->from("products");
$this->db->join('whs_products', 'products.id = whs_products.product_id');
$this->db->select("quantity");

I getting the list products that exists in whs_products not the sum. Some products are listed twice as they have 2 entries in whs_products.

I want list all the products once only where no quantity I want put 0 in quantity and where its is more than 1 in whs_products I want display sum of all the quantity

Help will be much appreciated!

Table Structure

Products
id, code, name, unit, price

whs_products
id, product_id, warehouse_id, quantity

I have whs table too for warehouse id, name, address

I tried this Sir,

$this->db->select("products.id as productid, products.code, products.name, products.unit, products.cost, products.price,   sum(whs_products.quantity) as 'totalQuantity'")
->from('products')
->join('whs_products', 'whs_products.product_id=products.id', 'left')
->group_by("products.id");
$this->db->get();

Every thing is fine. But the total number of products are calculated wrongly. I think system add 1 to total products, each time gets quantity from whs_products. For some products quantity is 2 or 3 time depending on each warehouse.

Any solutions for this. I am very thankful for your support.

Can you provide us the structure of your tables, what you have with your current request, and what you expect please ? — BMN
– BMN, Commented Dec 26, 2012 at 10:23

bonCodigo · Accepted Answer · 2012-12-27 08:05:01Z

4

Please try out the following query and comment.

SQLFIDDLE DEMO

Sample data:-

Products

PID     PNAME
1   j
2   k
3   m

whs_Products

WID     PID     QUANTITY
11  2   300
11  2   200
14  2   500
11  1   300
15  3   100
14  3   800

Query to get total by pid in whs_products

select pid, wid, sum(quantity) 
from whs_products
group by pid, wid
;

Results:

PID     WID     SUM(QUANTITY)
1       11      300
2       11      500
2       14      500
3       14      800
3       15      100

query using a variable to get user input for pid and by pid, wid

-- group by pid and wid
set @var:='2'
;
select a.pid, b.pname, a.wid, sum(a.quantity) 
from whs_products a
join products b
on b.pid = a.pid
where a.pid = @var
group by a.pid, wid
;

Results:

PID     PNAME   WID     SUM(A.QUANTITY)
2       k   11  500
2       k   14  500

final query to show quantity by user input pid only

Query:

-- by pid only    
set @var:='2'
;
select a.pid, b.pname, sum(a.quantity) 
from whs_products a
join products b
on b.pid = a.pid
where a.pid = @var
group by a.pid
;

Results:

PID     PNAME   SUM(A.QUANTITY)
2       k   1000

Since OP wants in CodeIgniter

Here is a headstart for you to try. At first I had the impression you already know the syntax of codeigniter and you are looking for SQL logic, so you could convert it into the desired format you need.

$this->db->select("a.pid, b.pname, count(a.quantity) as 'toalQuantity'");
$this->db->from('wsh_products a');
$this->db->join('products b', 'a.pid=b.pid', 'inner');
$this->db->group_by("a.pid"); 
$where = "a.pid = 2";
$this->db->get();
$query->results_array();

Or write a funciton :) :

function getQuantity($prodid = false)
{
  $this->db->select(a.pid, b.pname, count(a.quantity) as 'toalQuantity');
  $this->db->join('wsh_products a', 'a.pid=b.pid');
  if ($prodid !== false)
    $this->db->where('a.pid', $prodid);
  $query = $this->db->get('products b');

  if($query->result() == TRUE)
  {
    foreach($query->result_array() as $row)
    {
      $result[] = $row;
    }
    return $result;
  }
}

Edit as OP requested for `LEFT JOIN` in comments

SQLFIDDLE

To show all products in Products table, do the following:

In select show pid from Products table.
Use from Products Left Join Whs_Products
Group by pid from Products table

edited Dec 27, 2012 at 8:05

answered Dec 26, 2012 at 10:28

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

bonCodigo Over a year ago

@Saleem I am not personally using codeigniter. But I could find out some helpful tips along this answer :)

Saleem Over a year ago

You are great! It worked. but changed a and b with table names. Last Question Now its showing the products that exist in whs_products. I want list all the products in products table and where its isn't in whs_products i want show 0 in quantity. help!

Saleem Over a year ago

Here SQLfiddle sqlfiddle.com/#!2/3b178/5 Its showing only 3 products I want all products last 2 with 0 quantity. Thank you for help

bonCodigo Over a year ago

@Saleem getting all products in Products table although they might not be in whs_Products table is mainly depending on LEFT JOIN. In your question it was not mentioned. So I gave you the inner join. But I was thinking you may need the LEFT JOIN. SQLFiddle is a sample. And please know SQLFiddle is a sample. So in your real tables you may add all the data and try out :)

bonCodigo Over a year ago

@Saleem Please take a look at the update. And for the benefit of all of us in the community, could you pleaes update your question (append it) with the correct working code ignitor query code? :) Thanks

|

Collectives™ on Stack Overflow

Getting Sum of db field using join active record in codeigniter

1 Answer 1

Since OP wants in CodeIgniter

Edit as OP requested for `LEFT JOIN` in comments

15 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Since OP wants in CodeIgniter

Edit as OP requested for LEFT JOIN in comments

15 Comments

Your Answer

Sign up or log in

Post as a guest

Related

Edit as OP requested for `LEFT JOIN` in comments