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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
