Need Help with a Query

[eluser]vincej[/eluser]
Hi - I'm new to CI so please forgive what might be a silly question:

I have a table ( sample below) with Order ID, Product ID's and Ordervalues. I want to be able to sum the order value for each Product ID which are repeated by OrderID so that I get a single result which I can pass through my controller into my view which might look like: Total Order Value: Chickens: $5000.

Sample table:

Code:

orderid  prodid  ordervalue
DEA2806  25  25.00
DEA3216  23  16.00
BRO0289  22  35.00
BRO0289  17  18.00
BRO0289  18  43.00
DEA7759  28  62.00
JAC5354  17  240.00
DOE7762  17  240.00

So far my approach has been to first run a query which pulls the ProdID and Ordervalue. Put that into an array.

then run a second query, where I take the ProdID's from the array, and then do a sum on each one by ProdID.

Needless to say, my code does not do what I want it to do hence why I'm here. The first section, pulls to the ProdID's fine but does not give the order values - I don't understand why, similar queries have given multiple results.

My section 2 is probabaly completely wrong as all I am getting is the sum total of all products sold.


Model:

Code:

function ProductTotal(){
$result = array();    // Section one
$this->db->select('ProdID','ordervalue');
$this->db->from('order');
$this->db->where('status', 'open');
$Q = $this->db->get();
foreach ($Q->result_array() as $row){
    $result[] = $row;
}
  
  foreach ($result as $value){  //Section two
$this->db->select_sum('ordervalue');
$this->db->from('order');
$this->db->where('status', 'open');
$Q = $this->db->get();
foreach ($Q->result_array() as $row){
    $productorder[] = $row;
}
return $productorder;}
  }

If you help me out - I'd be super happy and super greatfull !

Many thanks !

[eluser]achilleusrage[/eluser]
You can do this with one query using a GROUP BY clause:

Code:

$this->db->select('prodid');
$this->db->select_sum('ordervalue');
$this->db->from('order');
$this->db->where('status', 'open');
$this->db->group_by('prodid');

[eluser]vincej[/eluser]
Sometimes the obvious defeats us ! Many Thanks ! You are a super star !