How to combine calculated fields with the same foreign key?

English
, , ,
#1

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.

#2

Hello,

Let me start with some missing (and important) information. Don’t know what specific version of LibreOffice you are using. Don’t know what database you are using. Can’t say default since depending upon the version it may be either HSQLDB embedded or Firebird embedded. The database matters since SQL can vary among databases.

The problem description is utterly confusing. For example:

one item can have multiple invoices

is like the vet saying you can pick up your dog but will need to go to two locations!

An educated guess here is that the Item table is one containing product description and maybe some related information. Your final SQL supposition may negate what was just stated. It really makes no sense.

Please edit your question by adding to its’ end the information missing and a better description of what you have (like table definitions and fields) or better yet, add a sample (no personal or confidential information).

Please do not use an answer for this.

#3

Basically what the part you quoted means is that the same item can be ordered multiple times with different costs, and yes the Item table contains the product description and such. So when the Invoice table lists an item, it lists the foreign key to the Item table.

I added the info you asked for in an edit but managed to find a workable answer already. Not sure if it’s the ideal one, but it seems to do what I want.

#4

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 "ItemID", (SUM( "Adjusted Unit Price" * "Quantity") / SUM("Quantity")) AS
"Average Adjusted Unit Price" 
FROM (
    SELECT "Item"."ItemID", ( "ItemInvoiceMoldSetup" + "ItemInvoicePrintSetup" + 
    "ItemInvoiceShipping" + "ItemInvoicePaypal" + ( "ItemInvoiceUnitPrice" *
    "ItemInvoiceQuantity" ) ) / "ItemInvoiceQuantity" AS "Adjusted Unit Price",
    "ItemInvoiceQuantity" AS "Quantity" FROM "ItemInvoice", "Item"
    WHERE "ItemInvoice"."ItemID" = "Item"."ItemID" 
    )
GROUP BY "ItemID"
#5

So, it would be better to actually say an invoice can contain the same item multiple times - each with a different price. As a customer I would want to see each item separate to insure the proper information was applied. And it still doesn’t make sense. Why are you AVG and not SUM? Aren’t you wanting to total multiple items?

#6

No, an invoice always contains only one item. These aren’t customer invoices, they’re ours from our manufacturer. If we order an item a second time, there are no longer setup fees, so our overall unit price for the item will now be the average of the first time we ordered it and the second time we ordered it. Though now that I think about it, it’s should be a weighted average based on the quantity of items, because this way only works if we order the same amount the second time.

This ‘adjusted unit price’ is used to calculate the profit per order later in the system. Customer invoices are a separate table.

#7

Based upon this new information, I would get the total for all the items, then add in the setup fees and finally divide by the total ordered thus far. Now you have current cost. Of course this changes each time you order again.

I also would not use an HSQLDB embedded database in most cases (if that is in fact what you are using - not stated as what is noted could be a split DB).

#8

I’m unsure of how I would go about using something else for it, as when I created the DB the only option in the dropdown was HSQLDB?

#9

HSQLDB is delivered with LibreOfiice/Base and can be used embedded (Data is in the .odb-File).
But you can connect to several other databases (any you can get a working connector) like MySQL, MariaDB, Postgres, SQlite. First steps are described in the Documentation for Base - but YOU have to decide wich suits your needs…)

J.

#10

Thank you! It’s just for a very small business (and is all just being run out of Google Sheets for now until I remember enough of my SQL classes in college to get everything working…) so I think the embedded one will probably be fine?

#11

Just letting you know. HSQLDB embedded has been know to occasionally lose data - some or all. This mostly occurs with improper shutdown on laptops but others have occurred.

This is commonly known with many Base users and posted numerous times.

Backup, Backup, Backup!!!

#12

Thank you, good to know!

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.