[Solved] Complicated Calculation in Query

Discuss the database features

[Solved] Complicated Calculation in Query

Postby OOPleaseHelp » Tue Jun 24, 2014 3:23 am

Hey all-

It is really a simple calculation in a spreadsheet but I am getting sql syntax errors when I try to perform it within base.

So far I have been able to SELECT the fields I need from two joined data tables that and then do simple multiplication and addition and create new calculated columns with aliases.

The problem I am having is when I try to use parentheses to perform subtraction, multiplication and addition, and then division all in one field.

IE: (A - ( (B*C) + D + E) ) / A

Ideally I would like to multiply the whole thing by 100 for a percentage but I will take what I can get!!!

From my research over the past few days it appears that I need to create a derived table or subquery using the new calculated aliases to make the large calculation simpler and SQL compatible but I am having a difficult time figuring it out.

Thanks!
Last edited by OOPleaseHelp on Tue Jun 24, 2014 8:47 pm, edited 1 time in total.
Open Office 4.0.1 on MAC OSX 10.6.8
OOPleaseHelp
 
Posts: 20
Joined: Tue Jun 24, 2014 3:11 am

Re: Complicated Calculation in Query

Postby OOPleaseHelp » Tue Jun 24, 2014 4:30 pm

This is the post I think answers the question but I couldn't make it work....

https://forum.openoffice.org/en/forum/v ... 1&p=298338

Thanks!
Open Office 4.0.1 on MAC OSX 10.6.8
OOPleaseHelp
 
Posts: 20
Joined: Tue Jun 24, 2014 3:11 am

Re: Complicated Calculation in Query

Postby Sliderule » Tue Jun 24, 2014 4:54 pm

What would help me, so I ( and other forum posters ) can help you . . . please:

  1. Post here, your Query that does work . . . withOUT the additional calculation you want. The reason for this is, to show exactly the table and column names defined in your database. NOTE: This will start with the word . . . SELECT

  2. Describe, in words, the additional calculation you want the database ( Query ) to perform.

  3. Please include in your post, the database back-end you are using. You can determine this, after opening your Base file, on the Status Bar, at the bottom . . . it will include the database engine. If it says, HSQL, please be sure to include here, the words Embedded database if these words are on your Status Bar.
Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1207
Joined: Thu Nov 29, 2007 9:46 am

Re: Complicated Calculation in Query

Postby OOPleaseHelp » Tue Jun 24, 2014 5:29 pm

SELECT
"Inventory"."ProductName",
"Sales"."DateSold",
"Inventory"."UnitPrice",
"Sales"."Quantity",
"Inventory"."UnitPrice" * "Sales"."Quantity" AS "ExtUnitCost",
"Sales"."PayPal",
"Sales"."eBay",
"Sales"."PayPal" + "Sales"."eBay" AS "TotalFees",
"Sales"."Mailer",
"Sales"."ShipCost",
"Sales"."PurchasePrice"
FROM "Sales", "Inventory" WHERE "Sales"."ProductName" = "Inventory"."ProductName"

That works currently. I want to add a field like alias "ExtUnitCost" and "TotalFees" that will calculate profit percentage.
IE: (("Sales"."PurchasePrice" - ("ExtUnitCost" + "TotalFees" + "ShipCost") ) / "Sales"."PurchasePrice" )* 100 AS "Profit"

I have tried it breaking the "ExtUnitCost" calculations out as well as the other subtotals for an even more complicated logic and that wont work either. I think it is the double parentheses or something.

Mine does say embedded database and HSQL database engine for some reason. I am at my work windows 7 machine now but at home I use mac osx.

Thank you for your time!
Open Office 4.0.1 on MAC OSX 10.6.8
OOPleaseHelp
 
Posts: 20
Joined: Tue Jun 24, 2014 3:11 am

Re: Complicated Calculation in Query

Postby Sliderule » Tue Jun 24, 2014 6:05 pm

Please try / run the following Query. I added the new calculated column of "Profit" at the end (of the SELECT clause ), but, of course, you can move this to wherever you desire in the Query. :)

Also, I qualified each column name with a table name . . . in the event you ever have the same names in your tables. This way, the database engine will know exactly which column you are referring to. Also, the calculation for "Profit" is performed with each individual table and column name, rather than using any other pre-calculated column name. Also, just as an important FYI . . . the number of decimal places in the calculated result will be the same as the greatest number of decimal places in the column names for that calculation. So, for example, if you wanted it ( the result ) to contain 4 ( four ) decimal places, simply change * 100 to * 100.0000 . . . so . . . the calculated result will now have an accuracy of four decimal places.

  1. Start your OpenOffice / LibreOffice Base file.
  2. On the left, click on the Queries icon
  3. Under Tasks, click on: Create Query in SQL View...
  4. Copy and paste the Select state below
  5. Run the Query by either:
    1. Press F5
    2. On the Toolbar, press the Run Query (F5) icon
    3. From the Menu: Edit -> Run Query
Code: Select all   Expand viewCollapse view
SELECT
   "Inventory"."ProductName",
   "Sales"."DateSold",
   "Inventory"."UnitPrice",
   "Sales"."Quantity",
   "Inventory"."UnitPrice" * "Sales"."Quantity" AS "ExtUnitCost",
   "Sales"."PayPal",
   "Sales"."eBay",
   "Sales"."PayPal" + "Sales"."eBay" AS "TotalFees",
   "Sales"."Mailer",
   "Sales"."ShipCost",
   "Sales"."PurchasePrice",
   (("Sales"."PurchasePrice" - (("Inventory"."UnitPrice" * "Sales"."Quantity") + ("Sales"."PayPal" + "Sales"."eBay") + "Sales"."ShipCost") ) / "Sales"."PurchasePrice" ) * 100 AS "Profit"
FROM "Sales",
     "Inventory"
WHERE "Sales"."ProductName" = "Inventory"."ProductName"

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to ad [Solved] in your 1st post Subject (edit buton top right) if this isue has ben resolved.
User avatar
Sliderule
Volunteer
 
Posts: 1207
Joined: Thu Nov 29, 2007 9:46 am

Re: Complicated Calculation in Query

Postby OOPleaseHelp » Tue Jun 24, 2014 8:47 pm

Wow Sliderule you rock! I don't know what I did wrong but yours works! Thanks!
Open Office 4.0.1 on MAC OSX 10.6.8
OOPleaseHelp
 
Posts: 20
Joined: Tue Jun 24, 2014 3:11 am

Re: Complicated Calculation in Query

Postby Sliderule » Tue Jun 24, 2014 8:57 pm

OOPleaseHelp wrote:Wow Sliderule you rock! I don't know what I did wrong but yours works! Thanks!

Thanks for marking this SOLVED.

The reason the Query as originally wrote it failed, is:

  1. You defined a new column . . . for example, "ExtUnitCost" . . . and . . . since it is NOT included in any of tables, or views in your FROM clause . . . you canNOT use it in another calculation.

    That is why I wrote, in my explanation above:

    Sliderule wrote:Also, the calculation for "Profit" is performed with each individual table and column name, rather than using any other pre-calculated column name.

  2. In the NEW column, I merely, included the calculation so, it ( database back-end ) can calculate it. And, yes, you may have an almost unlimited number of parentheses included in the calculation . . . assuming . . . they are all matched. That is, if a opening parenthesis, must be a matching ending parenthesis. :)
Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1207
Joined: Thu Nov 29, 2007 9:46 am


Return to Base

Who is online

Users browsing this forum: No registered users and 2 guests