The title isn’t entirely accurate to my problem I’m sure but I couldn’t think of how to word it.
I’m using a view to calculate the adjusted unit price of an item after accounting for initial setup costs, so the calculated field looks something like this:
"SetupFee" + "Shipping" + ( "UnitPrice" * "Quantity" ) / "Quantity"
The table these fields are from is called ItemInvoice and has a foreign key to a table called just Item, which contains item-specific information such as the description. The problem I’m having is that this method will give me two different outputs when there are multiple invoices for an item. For instance, if I have one invoice for item 1 and two for item 2 it shows me:
Item1 - 45.11 Item2 - 60.30 Item2 - 50.67
I’m really rusty on my SQL and databases in general (and am using LO Base for the first time) and am wondering how I would combine the costs for both invoices and divide them both by the total quantity. So something like:
("SetupFee1" + "Shipping1" + ( "UnitPrice1" * "Quantity1" ) + "SetupFee2" + "Shipping2" + ( "UnitPrice2" * "Quantity2" )) / ("Quantity1" + "Quantity2")
So far I’ve made this view entirely in Design View, but I’m not averse to using SQL if I know what I’m going to be doing.
EDIT: I’m using 7.0 LibreOffice and the HSQLDB database.