mysql - Simplifying my SQL with LEFT JOIN on multiple lines -

i reasonably experienced mysql , efficient queries now, still run issues...

i have 2 tables, "invoices" , "salesrecords" invoices table contains basic overview of invoice, , sales records breaks down specific information line line regarding items purchased.

for simplicity, let's assume tables this:

invoices

id    |   repaircharge     |    shippingcharge      |     total  1    |       28.95        |         0              |     30.45     2    |       10.00        |         8.50           |     29.50 

// note; total column includes items shown in sales records

salesrecords

invno  |    itemno   |  price    |   quantity   | discount  |      1    |     123     |  1.50     |      1       |     0     |     2    |     121     |  6.50     |      1       |     1.5   |     2    |     128     |  5.50     |      1       |     0     |   

i want total value of items sold, labour charge , shipping charges within date period, need to:

1. sum rows in invoices table
2. join (left?) salesrecords table on invno , sum ((price-discount)*quantity)
3. use clause date period

i write sql:

select sum((sr.price-sr.discount)*sr.quantity) income,       sum(i.repaircharge) labour,    sum(i.shippingcharge) carriage  invoices      left join salesrecords sr on sr.invno=i.id i.datetime between 1431647999 , 1434360348` 

and wrong

because, multiple instances of both tables invoices has more 1 item line, repaircharge , shippingcharge values doubled, or trebled etc depending on how many product lines purchased on invoice.

so, mess around , come solution... pretty ugly , presumably inefficient:

select sum(income) income,        sum(labour) labour,        sum(carriage) carriage  (select (select sum((price-discount)*quantity)               salesrecords               invno=i.id               group invno) income,               sum(i.repaircharge) labour,              sum(i.shippingcharge) carriage        invoices        i.datetime between 1434326474 , 1434361694       group id      ) totals 

can advise best way simplify , make more efficient?

in terms of legibility, , performance in other dbms sum sales records in sub-query:

select  sum(coalesce(sr.income, 0)) income,         sum(i.repaircharge) labour,         sum(i.shippingcharge) carriage    invoices         left join         (   select  invno, sum((price-discount)*quantity) income                salesrecords             group invno         ) sr             on sr.invno = i.id   i.datetime between 1434326474 , 1434361694; 

mysql uses intermediate materialisation on subqueries(as far aware), , if may adversely impact above query, since first sum records in salesrecords , store results in hash table before applying filter on invoice date, version of effort, using join rather correlated subquery may perform better:

select  sum(coalesce(i.income, 0)) income,         sum(i.repaircharge) labour,         sum(i.shippingcharge) carriage    (   select  i.id,                     i.repaircharge,                     i.shippingcharge,                     sum((sr.price - sr.discount) * sr.quantity) income                invoices                     left join salesrecords sr                         on sr.invno = i.id               i.datetime between 1434326474 , 1434361694             group i.id, i.repaircharge, i.shippingcharge         ) i;