Hi All, I'm stumped on this one, though the answer is probably staring me in the face, so I hope someone can help.
I have three tables (simplified here): Invoices, Orders and Moves. Each Invoice to a customer is for one or more products moved to them (ie sold to them). Each one of those products is moved (ie bought) from a supplier, incurring one or more Orders to satisfy each Invoice. create table "Invoices" -- invoice to a customer for goods ( ID integer primary key , Delivery real -- price charged to customer for delivery ) ; create table "Orders" -- order of goods from a supplier ( ID integer primary key , Delivery real -- cost of delivery for the order ) ; create table "Moves" -- movement of product from supplier to customer ( ID integer primary key , Code text -- product code , Quantity integer -- number of units of this product , Invoice integer --> Invoices.ID , "Order" integer --> Orders.ID , Sell real -- sell price as shown in the invoice , Buy real -- buy price as shown in the order ) ; For instance, I invoice a customer for 5 products, which I order from three different suppliers, like this: insert into "Invoices" values ( 10001, 22.0 ); insert into "Orders" values ( 11017, 12.0 ); insert into "Orders" values ( 11018, 10.0 ); insert into "Orders" values ( 11021, 15.0 ); insert into "Moves" values ( 28, 'CMH300', 1, 10001, 11017, 39.0, 31.47 ); insert into "Moves" values ( 29, 'IGS032', 1, 10001, 11017, 98.0, 79.51 ); insert into "Moves" values ( 30, '10027', 1, 10001, 11018, 98.0, 80.0 ); insert into "Moves" values ( 31, 'APKNANIN', 1, 10001, 11018, 13.0, 10.0 ); insert into "Moves" values ( 32, 'GG1501007', 1, 10001, 11021, 10.0, 8.0 ); I can calculate the total Income per Invoice by: select "Invoices".ID as Invoice, Delivery, sum(Sell), Delivery + sum(Sell) as Income from "Invoices" left join "Moves" on "Invoices".ID = "Moves".Invoice group by Invoice ; which gives: Invoice Delivery sum(Sell) Income 10001 22.0 258.0 280.0 and the total Expense per Order by: select "Orders".ID as "Order", Delivery, sum(Buy), Delivery + sum(Buy) as Expense from "Orders" left join "Moves" on "Orders".ID = "Moves"."Order" group by "Order" ; which gives: Order Delivery Sum(Buy) Expense 11017 12.0 110.98 122.98 11018 10.0 90.0 100.0 11021 15.0 8.0 23.0 I want to get the total Expense per Invoice. How can this be done correctly and efficiently to give: Invoice sum(Buy) sum("Orders".Delivery) Expense 10001 208.98 37.0 245.98 I can get the total Expense (without Delivery) for the product Moves: select "Invoices".ID as Invoice, sum(Buy) from "Invoices" left join "Moves" on "Invoices".ID = "Moves".Invoice group by Invoice ; which gives: Invoice sum(Buy) 10001 208.98 If I try to include the Delivery like this: select "Invoices".ID as Invoice, sum(Buy), sum("Orders".Delivery) from "Invoices" left join "Moves" on "Invoices".ID = "Moves".Invoice left join "Orders" on "Moves"."Order" = "Orders".ID group by Invoice ; it gives an incorrect sum of Delivery, since it adds the delivery per Move instead of per Order: Invoice sum(Buy) sum("Orders".Delivery) 10001 208.98 59.0 I tried this: select "Invoices".ID as Invoice , (select sum(Buy) from Moves where Moves.Invoice = Invoices.ID) as "Sum Buy" , (select sum(Delivery) from "Orders" on "Orders".ID in (select "Order" from Moves where Invoice = Invoices.ID)) as "Sum Delivery" from "Invoices" group by Invoice ; which works correctly but it seems inelegant, inefficient and in one test seems orders of magnitude too slow. Any ideas? Thanks, Tom _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users