CODEWITHSUNDEEP
mysqlgroup-by
charlie
charlie

Reputation: 481

SQL JOIN three tables to obtain total values

adhocbills
- sequence
- invoice_number

adhocbills_lineitems
- sequence
- bill_seq
- unitprice
- quantity
- service

billing_invoices
- sequence
- invoice_number
- datetime
- sub_total

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

select * from billing_invoices 
where DATE(datetime) >= 'x' AND DATE(datetime) <= 'x'

but then i need to select the equivilent row from adhocbills using:

billing_invoices.invoice_number = adhocbills.invoice_number

then i need to select all rows from adhocbills_lineitems using:

adhocbills.sequence = adhocbills_lineitems.bill_seq

and from adhocbills_lineitems get the totals and group by service

the total i want to return from billing_invoices.sub_total

i tried this query, joining all tables:

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 <> 'Y' 
  AND i.invoice_type = 'Invoice' 
  AND a.status = 'Completed' 
  AND DATE(i.datetime) >= '2016-09-01' 
  AND DATE(i.datetime) <= '2016-09-30' 
GROUP BY l.service 
ORDER BY grand_total DESC

but that is returning the incorrect values

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

Service 1 / 50
Service 2 / 30+20 = 50

sample data:

adhocbills table:

sequence = '1'
type = 'Invoice'
invoice_number = '1234'

adhocbills

adhocbills_lineitems table:

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'

adhocbills_lineitems

billing_invoices table:

sequence = '1'
invoice_number = '1234'
sub_total = '100'
vat_amount = '20'
grand_total = '120'
datetime = '2016-09-01'
invoice_type = 'Invoice'

billing_invoices

so from this sample data, when the 'service' column is grouped in adhocbills_lineitems the result should be:

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

Upvotes: 0

Views: 49

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

Your table structure does not match select query. This answer is based on your information about relation between tables and expected output.

SELECT 
  l.service,
  SUM(l.unitprice) AS 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.datetime BETWEEN '2016-09-01' AND '2016-09-30'
GROUP BY
  l.service
ORDER BY
  total DESC

This matches your expected output. You should be summing by unitprice, not sub_total as you've mentioned in your question.

Should there by any more constraints add them to WHERE clause.

I've simplified date conditions to be able to use plain index on datetime field since MySQL doesn't support indexes on expressions.

Upvotes: 1

Related Questions