How to combine calculated fields with the same foreign key?

Question

The title might not be entirely accurate to my problem 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:

ItemName - (Calculation)
Item1 - 45.11
Item2 - 60.30
Item2 - 50.67

I'm really rusty on my SQL and databases in general (and am using LibreOffice 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")

...or just averaging the two separate results, though I'm not sure how that would change the decimal values.

So far I've made this view entirely in LibreOffice Base's Design View, but I'm not averse to using SQL if I know what I'm going to be doing.

EDIT: LibreOffice 7.0 and embedded HSQLDB datbase.

Answer 1

Okay so I think I've figured it out, though I feel like there should maybe be a simpler way of doing it.

Definitely had to delve into SQL statements on this one, but this seems to do what I want:

SELECT "Item Name", AVG("Adjusted Unit Price") AS "Average Adjusted Unit Price"
FROM (
    SELECT "Item"."ItemName" AS "Item Name", ( "SetupFee" + "Shipping" + ( "UnitPrice" * "Quantity" ) ) / "Quantity" AS "Adjusted Unit Price"
    FROM "ItemInvoice", "Item" WHERE "ItemInvoice"."ItemID" = "Item"."ItemID"
)
GROUP BY "Item Name"