sum with join in active records

Hi pleas bear with me I'm very new to mysql/active records (all previous projects have only been basic CRUD) I am trying to join two tables so I can generate a table of my invoices with a subtotal (database structure based on bamboo invoice)

currently trying with no luck (syntax error)

$this->db->select('invoice_number, dateIssued ');
$this->db->select('(SELECT SUM(amount * qty) FROM manage_invoice_items DISTINCT invoice_id) AS subtotal' , FALSE);
$this->db->from('manage_invoices');

$this->db->join('manage_invoice_items', 'manage_invoices.id = manage_invoice_items.invoice_id');
$this->db->where('client_id', $client_id);

$query = $this->db->get();
return $query->result();

with

$this->db->select('invoice_number, dateIssued');
$this->db->fro开发者_如何学运维m('manage_invoices');

$this->db->join('manage_invoice_items', 'manage_invoices.id = manage_invoice_items.invoice_id');
$this->db->where('client_id', $client_id);

$query = $this->db->get();

return $query->result();

I get results for each invoice item (I want one result per invoice with a subtotal of the invoice items for that invoice number)

Hope that all makes send like I said I don't know much about mysql (even reference to a good tutorial on combining functions would be handy.

---

I'm not personally familiar with CodeIgniter/ActiveRecord but it seems like what you want is to group the records using a GROUP BY function. I found this link that may be helpful. You probably want to use:

$this->db->select_sum("amount * qty");

and

$this->db->group_by("invoice_number");                               

---

    $this->db->select('invoice_number, dateIssued');
    $this->db->select('ROUND((SUM(amount * qty)), 2) AS subtotal', FALSE);
    $this->db->from('manage_invoices');

    $this->db->join('manage_invoice_items', 'manage_invoices.id = manage_invoice_items.invoice_id');
    $this->db->where('client_id', $client_id);
    $this->db->group_by('invoice_number');
    $query = $this->db->get();


    return $query->result();

thanks to Narthring (i didn't know about group_by)