SQL JOIN three tables to obtain total values
<pre><code>adhocbills
- sequence
- invoice_number

adhocbills_lineitems
- sequence
- bill_seq
- unitprice
- quantity
- service

billing_invoices
- sequence
- invoice_number
- datetime
- sub_total
</code></pre>

<p>I want to select all rows from <code>billing_invoices</code> for a certain date range which i am doing with this query:</p>

<pre><code>select * from billing_invoices 
where DATE(datetime) &gt;= 'x' AND DATE(datetime) &lt;= 'x'
</code></pre>

<p>but then i need to select the equivilent row from <code>adhocbills</code> using:</p>

<pre><code>billing_invoices.invoice_number = adhocbills.invoice_number
</code></pre>

<p>then i need to select all rows from <code>adhocbills_lineitems</code> using:</p>

<pre><code>adhocbills.sequence = adhocbills_lineitems.bill_seq
</code></pre>

<p>and from <code>adhocbills_lineitems</code> get the totals and group by <code>service</code></p>

<p>the total i want to return from <code>billing_invoices.sub_total</code></p>

<p>i tried this query, joining all tables:</p>

<pre><code>SELECT 
  COUNT(i.sequence) as counter, l.service as service, SUM(i.sub_total) as sub_total, SUM(i.total_charges) as total_charges, SUM(i.vat_amount) as vat_amount, SUM(i.grand_total) as grand_total 
FROM billing_invoices i 
LEFT JOIN adhocbills a ON a.invoice_number = i.invoice_number 
LEFT JOIN adhocbills_lineitems l ON a.sequence = l.bill_seq 
WHERE 
  i.proforma &lt;&gt; 'Y' 
  AND i.invoice_type = 'Invoice' 
  AND a.status = 'Completed' 
  AND DATE(i.datetime) &gt;= '2016-09-01' 
  AND DATE(i.datetime) &lt;= '2016-09-30' 
GROUP BY l.service 
ORDER BY grand_total DESC
</code></pre>

<p>but that is returning the incorrect values</p>

<p>for example, i have this data in my <code>adhocbills_lineitems</code> table so when these are grouped together by <code>service</code> so there would be 2 rows:</p>

<pre><code>Service 1 / 50
Service 2 / 30+20 = 50
</code></pre>

<p>sample data:</p>

<p>adhocbills table:</p>

<pre><code>sequence = '1'
type = 'Invoice'
invoice_number = '1234'
</code></pre>

<p><a href="https://s4.postimg.io/3zbz30pxn/Screen_Shot_2016_09_17_at_10_01_13.png" rel="nofollow"><img src="https://s4.postimg.io/3zbz30pxn/Screen_Shot_2016_09_17_at_10_01_13.png" alt="adhocbills"></a></p>

<p>adhocbills_lineitems table:</p>

<pre><code>sequence = '1'
bill_seq = '1'
unitprice = '50'
quantity = '1'
service = 'Service 1'

sequence = '2'
bill_seq = '1'
unitprice = '30'
quantity = '1'
service = 'Service 2'

sequence = '3'
bill_seq = '1'
unitprice = '20'
quantity = '1'
service = 'Service 2'
</code></pre>

<p><a href="https://s4.postimg.io/px8fwt4y3/Screen_Shot_2016_09_17_at_10_01_20.png" rel="nofollow"><img src="https://s4.postimg.io/px8fwt4y3/Screen_Shot_2016_09_17_at_10_01_20.png" alt="adhocbills_lineitems"></a></p>

<p>billing_invoices table:</p>

<pre><code>sequence = '1'
invoice_number = '1234'
sub_total = '100'
vat_amount = '20'
grand_total = '120'
datetime = '2016-09-01'
invoice_type = 'Invoice'
</code></pre>

<p><a href="https://s4.postimg.io/3zbz30pxn/Screen_Shot_2016_09_17_at_10_01_13.png" rel="nofollow"><img src="https://s4.postimg.io/3zbz30pxn/Screen_Shot_2016_09_17_at_10_01_13.png" alt="billing_invoices"></a></p>

<p>so from this sample data, when the 'service' column is grouped in <code>adhocbills_lineitems</code> the result should be:</p>

<p>Service 1 = 50 - (made up of 1 row = 50)
Service 2 = 50 - (made up of 2 rows = 30 and 20)</p>