Small Help With MySQL query

I'm hoping you may be able to provide a little direction on this.

I have several invoices and some invoices have more than one item.

I'm developing an ADM page that will display the amount owning in the total invoice per parent per billing cycle (weekly).

The code I have below displays the data I'm looking for but only looks at one item and not if the parent has more than one item. So, if the parent purchased a $200 item, and then a $100 item, the Mysql below would produce a $200 Total Invoice when it should read $300.

Could you help me with the code to figure out what I need to add to show the total cost of all the items per invoice for the parent?

$query = "SELECT AS 'Parent_Name', AS 'Parent_ID', AS 'Children', AS 'Invoice_ID',
o70vm_invoices_invoices.user_id AS 'Parent_ID_on_Invoice',
o70vm_invoices_invoices.invoice_num AS 'Invoice_Num',
o70vm_invoices_invoices.invoice_date AS 'Invoice_Date', AS 'Item_ID',
o70vm_invoices_items.invoice_id AS 'Invoice_ID_on_Items',
o70vm_invoices_items.value AS 'Fee',
o70vm_invoices_items.amount AS 'Qty',

ROUND((o70vm_invoices_items.value)*(o70vm_invoices_items.amount),2) AS 'INV-TOTAL'

FROM o70vm_invoices_invoices, o70vm_invoices_contacts, o70vm_invoices_items
WHERE = o70vm_invoices_invoices.user_id
o70vm_invoices_items.invoice_id =

o70vm_invoices_invoices.publish =1

HAVING o70vm_invoices_invoices.invoice_date = STR_TO_DATE('2014-05-12 00:00:00', '%Y-%m-%d %H:%i:%s')";

Thanks in advance,

Responses (2)
  • Accepted Answer

    Friday, May 16 2014, 09:46 AM - #Permalink
    well you need a SUM() function to get the totals frmo multiple rows.

    like SUM ( (o70vm_invoices_items.value)*(o70vm_invoices_items.amount) ) or whatever works in your case.
    The reply is currently minimized Show
  • Accepted Answer

    Friday, May 16 2014, 10:13 AM - #Permalink
    Awesome! Thanks. I feel so silly. :)
    The reply is currently minimized Show
Your Reply