Hi All,
> 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.
> 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
> 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 figured out a solution:
select Invoice , sum(Buy), sum(Delivery), sum(Buy + Delivery) as Expense
from (
select "Order", "Invoice", sum(Quantity * Buy) as Buy
from Moves
group by "Order"
)
left join "Orders" on "Order" = "Orders".ID
group by "Invoice"
;
which gives the desired sums without duplicating the delivery for two
items from the same order:
Invoice sum(Buy) sum(Delivery) Expense
10001 208.98 37.0 245.98
In more complex tests on a larger data set, this method takes less
than a second, compared to over a minute when using my previous best
method.
Thanks,
Tom
BareFeet
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
